h | 13 Feb 00:21 2016

Hobbyist question about curves

I use PostGIS and QGIS on an OpenSUSE machine with a fairly light level
of expertise.

I am interested in roads, and would like to be able to see / examine
curves / bends.

I have downloaded roads from OSM  (nodes and ways), brought these into
PostGIS as CSV files and am able to display them in

What I would like to do is convert these lines into curves.  I have
tried using "St_LinetoCurve(lineGeom)" to form the curves, but the
geometry returned appears identical to the original lines.

My main assumption is that if I pass St_LinetoCurve a line string,
it will return a multigeometry object containing lines and circular

How can I convert a line string into a collection of lines and circular

Also, once this is done, is there a command to return the radius of the
circular arcs created?

Many thanks

postgis-users mailing list
(Continue reading)

Carlo Fragni | 12 Feb 21:32 2016

Trouble generating raster image with fixed dimensions

Hello everyone,

I am trying to create fixed dimensions squared png tiles with raster data, but i am getting cropped irregular tiles with varying height and width depending on the values of the pixels. I tried a couple of things to work around this issue, like creating a squared raster and merging it with the cropped one but I can't manage to merge rasters of different dimensions. Can anyone give me a hand? Is there a better way to force the output image not to crop the parts of the squared area with no data?   

This query illustrates my issue:

select st_summary(ST_UNION(r))
from (

 select st_union(ST_Clip(rast,1,g::geometry,TRUE)) as r
  st_makebox2d(st_makepoint(0,0),st_makepoint(10,10)) as g,
  (select ST_AsRaster(p,1, 1, '2BUI') as rast
  from (
   select st_makepoint(5,2) as p union
   select st_makepoint(3,4)
  ) as rast
  ) as r
 select ST_AsRaster(st_makebox2d(st_makepoint(0,0),st_makepoint(10,10))::geometry,1, 1, '2BUI')

) as t

Any help is deeply appreciated.

Best regards,
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Pallavi Sontakke | 11 Feb 11:37 2016

PostGIS 2.2 Debian package based on Postgresql 9.5 for trusty ( ubuntu 14.04)

Hi All,

I was looking to install PostGIS 2.2  to work with Postgresql 9.5 on my trusty system. As I did not find any available debian packages for trusty, I was trying to build from PostGIS source. However, even after getting many other binaries, could not get all modules of PostGIS working (especially postgis_sfcgal and address_standardizer).

Could you please point me to trusty packages if available? Also, if not, could you please let me know when would they be available?

postgis-users mailing list
postgis-users <at> lists.osgeo.org
Lars Aksel Opsahl | 10 Feb 09:11 2016

A function for “Esri union” union on big tables on github.


There has been different mails about this topic lately. We have now have added the code we use to Github and
hopefully somebody can pick up some ideas or just use this function as it is.

The basic idea is that you call this function and with 2 tables as input. The following happens in the function

  *   Builds up a content based grid

  *   Computes the result

  *   Removes the grid lines from the result

  *   Returns a table name with the union of this two tables. For areas that intersect you get attributes from
both tables and for areas that only exits in one of the tables you only get attributes from one table.

The code is found at https://github.com/larsop/esri_union

About performance. The code added now runs in a single thread, but we have a slightly modified code that runs
in parallel using “Gnu parallel” and then we can increase the performance many times depending on how
many CPU you have on your server. Here is an example running with 20 threads.

num points num polygons table size

Table 1 40435700 1088614 637 MB

Table 1 933145431 7924019 10127 MB

Result table 2042294001 43668256 30 GB

The time used to do the intersection was 152 minutes. I will add the parallel code later when I have time to
make the code ready.


postgis-users mailing list
postgis-users <at> lists.osgeo.org
Rubio Vaughan | 8 Feb 18:38 2016

ST_3DIntersection slow/hanging with moderately complex shapes

Hi all,

I've noticed that ST_3DIntersection becomes quite slow when feeding it 
somewhat complex shapes.

SELECT PostGIS_Full_Version() returns:
"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" 
PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.0.1, released 2015/09/15" 
LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.1.4 
r12966" need upgrade) RASTER (sfcgal procs from "2.2.0 r14208" need 

The query below takes a polygon with 97 vertices and extrudes it 
vertically by 5 meters. It then performs an ST_3DIntersection with a 
simple rectangle, which is also extruded by 5 meters. This takes about 
15-20 seconds to run on our system, during which the postgres process 
appears to hang until the query completes. The query can not be 
cancelled or terminated, only a kill -9 will end the process.

The polygon in the query below is an extract of a larger polygon with 
2451 vertices. Trying to perform the same query with the large polygon 
results in a hanging postgres process.

I would think that the 3D intersection is a relatively simple operation 
which shouldn't be taking so long, but perhaps I'm expecting too much?

Best regards
Rubio Vaughan

Example query below:

shape1 AS (
  SELECT ST_GeomFromText('POLYGON((121800 487784.532276641,
  121800 487967.485626712,121804.579 487964.71,121804.77 487965.08,
  121815.025 487958.935,121875.85 488060.361,121845.279 488078.9,
  121838.669 488082.91,121834.079 488085.67,121827.787 488089.481,
  121841.234 488111.68,121843.325 488111.306,121845.608 488110.874,
  121847.888 488110.418,121850.162 488109.939,121852.431 488109.436,
  121854.695 488108.91,121856.953 488108.361,121859.206 488107.788,
  121861.452 488107.192,121863.692 488106.573,121865.926 488105.931,
  121868.153 488105.266,121870.373 488104.578,121872.586 488103.867,
  121874.791 488103.133,121876.988 488102.376,121879.178 488101.597,
  121881.359 488100.795,121883.533 488099.971,121885.697 488099.124,
  121887.853 488098.255,121889.999 488097.364,121892.136 488096.45,
  121894.264 488095.515,121896.382 488094.558,121898.49 488093.579,
  121901.641 488098.177,121904.879 488102.859,121908.136 488107.527,
  121911.413 488112.181,121914.709 488116.821,121918.025 488121.447,
  121921.36 488126.059,121924.715 488130.657,121928.089 488135.241,
  121931.483 488139.81,121934.895 488144.366,121938.327 488148.906,
  121941.778 488153.433,121945.248 488157.944,121948.737 488162.441,
  121952.245 488166.924,121980.002239962 488200,122032.222894779 488200,
  121983.281 488141.68,121982.997 488141.319,121979.545 488136.9,
  121976.113 488132.467,121972.699 488128.019,121969.305 488123.557,
  121965.929 488119.08,121962.573 488114.589,121959.236 488110.083,
  121955.918 488105.563,121952.62 488101.029,121949.341 488096.481,
  121946.081 488091.919,121942.842 488087.343,121939.621 488082.753,
  121936.421 488078.15,121933.24 488073.532,121943.262 488065.666,
  121946.55 488062.983,121949.786 488060.239,121952.971 488057.434,
  121956.101 488054.57,121959.178 488051.647,121962.198 488048.667,
  121965.162 488045.631,121968.068 488042.539,121970.916 488039.393,
  121980.701 488028.158,121966.434 488005.12,121934.299 488024.816,
  121873.573 487923.555,121879.31 487920.09,121879.079 487919.72,
  121884.85 487916.23,121885.06 487916.57,121895.39 487910.32,
  121894.649 487909.09,121898.989 487906.46,121919.445 487894.064,
  121882.984 487833.706,121843.375 487814.553,121800 487784.532276641))',
  28992) geom
shape2 AS (
  SELECT ST_MakeEnvelope(121820, 487790, 121900, 487870, 28992) geom
volume1 AS (
  SELECT ST_Extrude(ST_Force3D(geom), 0, 0, 5.0) vol
  FROM shape1
volume2 AS (
  SELECT ST_Extrude(ST_Force3D(geom), 0, 0, 5.0) vol
  FROM shape2
SELECT ST_3DIntersection(v1.vol, v2.vol) vol
FROM volume1 v1, volume2 v2
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Ricardo Pinho | 7 Feb 15:55 2016

[announce] GISVM 14.04.3 PostGIS Server companion


Just to announce to all PostGIS users the new GISVM PostGIS Server release.
It can be your sidecar server to add all the power of an Open Geospatial Database (PostgreSQL 9.5 + PostGIS 2.2), just right from your local computer. You can use it directly or with any GIS Desktop software, such as QGIS.

If interested, just give it a visit:
and thank you for supporting the project!

Have fun!
Ricardo Pinho
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Matthew Syphus | 7 Feb 05:05 2016

Tiger geocoder - error instead of null when unlocatable

After following the instructions (several times) and apparently successfully installing tiger geocoder and the tiger data for Idaho, I keep getting an error when the geocoder can’t find an address.


With a locatable address it works just fine.

SELECT * FROM geocode('3300 state st, boise, id 83703')


When an address is not locatable, I assume it should return null (and move on to the next in the batch).

Instead, it returns an error:


SELECT * FROM geocode(‘3300 state st, neverland, id 9876543210’)




ERROR:  column co.statefp does not exist

LINE 2: ...cefp = p.placefp)  LEFT JOIN county co ON ('16' = co.statefp...


QUERY:  SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,coalesce(p.name,zip.city,cs.name,co.name),s.stusps,sub.zip)    sub.predirabrv   as fedirp,    sub.fename,    COALESCE(sub.suftypabrv, sub.pretypabrv)   as fetype,    sub.sufdirabrv   as fedirs,    coalesce(p.name,zip.city,cs.name,co.name)::varchar as place,    s.stusps as state,    sub.zip as zip,    interpolate_from_address($1, sub.fromhn,    …



It doesn’t seem to matter which address element causes the problem, the error is the same and it kills the query.  Is there some error handling that is getting missed? The examples I’ve seen look like it just returns null.  Thank you for any help.





postgis-users mailing list
postgis-users <at> lists.osgeo.org
Alain Benard | 5 Feb 15:57 2016

Make Union between 2 layers

I would like to make an operation between two tables with geometry (polygon) - user's GIS call this operation a UNION.

I think this :
  • Step1 : make st_intersection (where st_intersects) and obtains the 4 little square with attributes from the two table. I call the result layerintersect
  • Step 2 : make a unique polygon with the layerintersect (Step 1)
  • Step 3 : use this polygon  with the two table and st_difference
  • assembly the 3 result (layerintersect + the 2 difference) with a classic union (SQL)

The second step make a big polygon and for other data it can be a problem ??
Is there another way to make this job?
Thanks in advance.

Attachment (alain_benard.vcf): text/x-vcard, 292 bytes
postgis-users mailing list
postgis-users <at> lists.osgeo.org
dave.potts | 5 Feb 10:13 2016

loading raster colour heatmap data or converting to a shape file

Hi list,

I have some heat map files which I want to do some spatial analyisis on.

I am wondering what the best way to proceed.  I admin that I have never used the raster part of postgis before.

I have two choices

1. Convert the raster files to shape files and load the shape files in to postgis.
2. Load the raster files and have a play with some of the raster operators.

Has anybody got any suggestions as to the best way to proceed?

The heatmap are geotiff files and are correctly geoferenced and can be any colour that I choose.

As a side issue are there any colour combinations that  are easier to convert form raster to vector for  example would a black/white image digitise better than an image coloured in many different  shades of orange?


postgis-users mailing list
postgis-users <at> lists.osgeo.org
Hemant Bist | 3 Feb 19:34 2016

Creating touching depth areas from gridded data

I have gridded data for ocean/lake(that gives depth of water at equidistant intervals) and I am trying to extract polygon and multipolygons that represent different water depth range.
example of gridded data set is  here , and the polygons  I am trying to create are ST_Multipolygon approximation of different color areas in this image

I tried the simple approach of st_union grid rectangles with st_snap_to_grid as mentioned here.
The problem is that the resulting polygons have zig-zag edges. If I do st_simplify or smoothening (grass v.generalize) I am left with polygons that are not touching anymore (there is a gap between polygons that were touching earlier).

Is there something in Postgis/Some other open source tool that I can use to avoid this problem: get smooth polygons that touch each other. I will appreciate any pointers.
There was some reference that "topology" may be able to help, but I am unable to find a tutorial/example that would load the data in postgis and extract smooth polygons.

postgis-users mailing list
postgis-users <at> lists.osgeo.org

compilation raster layers with postgis

Hi everybody !

For information, I work on :

postgis 2.1
postgre 9.4
Qgis 2.12.2

I try to compile together several raster layers with postgis. For information, layer "a" has priority over layer "b", and all of my rasters are single-band.

This is the code I enter :

DROP TABLE IF EXISTS lucie.compil;
CREATE TABLE lucie.compil
WHEN a.valeur>0 THEN a.rast
ELSE b.rast
END as rast
FROM lucie.test as a join lucie.final_raster as b on st_intersects(a.rast,b.rast);

But it doesn’t run...I can’t open the result layer on Qgis...Do you know why ?

Thank you very much for your help that would be so precious, because I have been searching for 5 months !!!

Chargée d'études SIG

Tel : 04 74 27 53 52

postgis-users mailing list
postgis-users <at> lists.osgeo.org