Dan Jacobson | 30 Jun 18:50 2016

Regularly spaced address markers on alternating side of road

Dear PostGis list, I want to make pseudo-addresses every 25 meters along
a road,

21  23  25  27  29
  22  24  26  28

Sort of like I did a long time ago in the images on
with a different GIS. But at that time I only put them on the centerline.

OK I found

So I suppose I should: (0. apt-get install postgis etc. here on Debian.)
1. Learn how to import my vector roads from .kmz into postgis. (I'll Google that.)

2. Use http://postgis.net/docs/ST_Length.html to get the length (L in meters) of each
of my roads.

3. Write the equivalent of perl:
for($a_fraction=0; $i <= 1; $house_number++, $a_fraction += 25/$L){print $house_number at
location (ST_Line_Interpolate_Point(..., $a_fraction));}

4. For extra bonus, figure out to offset the label:
perpendicular to the road at 3 * ($address % 2 ? -1 : 1) meters.


>>>>> "RH" == Régis Haubourg <regis.haubourg <at> gmail.com> writes:
(Continue reading)

Neumann, Andreas | 30 Jun 11:25 2016

Question on topology

Hi all,

I have a table with CurvePolygon geometry and I'd like to extract CompoundCurve or Linestring geometry for the purpose of landcover symbology in a complex map design. The common polygon borders should exist in the data only once, but I'd need to know which land cover types (attached to the original CurvePolygons) are left and right of the border.

Is there a method in Postgis to create a Topology, eliminating double borders from the original CurvePolygons and attaching left and right attributes from the original polygons? I came across the Postgis topology module. Is this the recommended way of achieving my above goal?

Unfortunately, there are only few ressources or examples around Postgis topology and from the first glimpse it looks a bit complicated to use. It also looks like it can't handle CurvePolygons as input - right? I would have to coerce first to POLYGON geometries - right? It is ok for me if the circular arcs are segmentized, as this data is not for editing/analysis, but only for symbology purposes.

If you have any hints or examples, please let me know!




postgis-users mailing list
postgis-users <at> lists.osgeo.org
sablok | 27 Jun 18:55 2016

Encountering error while installing postgis from source?

I am using the following command to make and make install postgis-2.2.2 from
source and I have all the other dependencies configured properly -

cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& ./configure --with-pgconfig=${build_root}/bin/pg_config
--with-jsondir=${build_root} --with-projdir=${build_root}
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& make -j 4
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& make clean
cd ${postgis_src_path}/configuration/postgis/postgis-$POSTGIS_BUILD_VERSION
&& make install
&& make
&& make install

After the above commands execute the configure step terminates gracefully
with all the configuration files set up properly and I can see the following
on my screen

PostGIS is now configured for x86_64-unknown-linux-gnu

The make process however fails with make: *** [all] Error 1  and I see the
following message on my screen

lwout_wkb.c: In function ‘empty_to_wkb_buf’:
lwout_wkb.c:319:22: error: ‘NAN’ undeclared (first use in this function)
   static double nn = NAN;
lwout_wkb.c:319:22: note: each undeclared identifier is reported only once
for each function it appears in
make[1]: *** [lwout_wkb.lo] Error 1
make[1]: *** Waiting for unfinished jobs....

does anyone have any idea as to why is this happening, I searched about it
and it seems to be related to GCC erroring out. I am not sure as this is my
first experience with building postgis. Thanks in advance. 

View this message in context: http://postgis.17.x6.nabble.com/Encountering-error-while-installing-postgis-from-source-tp5010198.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
postgis-users mailing list
postgis-users <at> lists.osgeo.org
h | 26 Jun 13:22 2016

Looking for help / documentation on curves

I am wanting to use curves (arcs) for representing roadways.
I am having trouble finding documentation on curves.

My googling brings up articles like:


and others dated around 2012.

Can someone direct me to some current tutorials or documentation on
using curves in PostGIS?

I am particularly interested in calculating radii, but would appreciate
a general article on curves..

Many thanks,
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Citterio, Michele | 24 Jun 18:56 2016

Stackbuilder binaries and out-of-db raster formats



I’m looking into out-of-db ESA Sentinel-2 imagery. The Windows binaries from Stackbuilder are compiled without support for the GDAL drivers JP2OpenJPEG and SENTINEL2. The latter actually requires gdal 2.1, but I could get going with just jp2 support in the meantime.


I assume Stackbuilder binaries will be compiled against gdal 2.1 starting from Postgis 2.3, but is there any plan to include more GDAL drivers, ideally all those included with the GDAL utilities from OSGeo4W? Of course they would need to be explicitly enabled using postgis.gdal_enabled_drivers.





postgis-users mailing list
postgis-users <at> lists.osgeo.org
Regina Obe | 22 Jun 13:24 2016

PostgresOpen 2016 - Conference and PostGIS Intro Training Workshop

Postgres Open 2016  just released the schedule.

The conference runs Sept 13th-15th 2016, in Dallas, TX with the Sept 13th
dedicated to Trainings.
Each Training is $200/session (half-day) and priced separately from the

Sept 14th-15th will be main conference

Shameless Plug:  We'll be giving an Introduction to PostGIS workshop on Sept


So if you know anyone interested in some PostGIS training, let them know.


postgis-users mailing list
postgis-users <at> lists.osgeo.org
Wolfgang Glas | 18 Jun 21:44 2016

Strange EPSG:3857 transformation results.

Hi all,

  I desperately tried to recalculate WGS84/EPSG:3857 (pseudo-mercator)
transformations and have really weird results using postgis 2.2.2 with
postgres 9.5.3

First example is the origin 0/0 of WGS84:

#  select ST_AsText(ST_Transform(
    ST_GeomFromText('POINT(0.0 0.0)',4236),3857));
  POINT(-549.033225716555 -204.380569358876)
 (1 row)

To my understanding the origin of EPSG 3857 is also the origin of EPSG
4326/WGS84, so the result seem to be plain wrong to me :-/

Another unit test was:

# cs2cs +init=epsg:3857 +to +init=epsg:4326 -f %.17lg
 1400000 6000000
 12.576413977673299 47.353704702433106 0

For this test, postgis gave different values as in:

# select ST_AsText(ST_Transform(
   ST_GeomFromText('POINT(1400000 6000000)',3857),4236));
  POINT(12.5816693645354 47.3508570080732)

According to my research, the results of cs2cs are exact and e.g. in
line with http://wiki.openstreetmap.org/wiki/Mercator

My postgres/postgis versions are:

PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian
4.9.2-10) 4.9.2, 64-bit POSTGIS="2.2.2 r14797" GEOS="3.4.2-CAPI-1.8.2
r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released
2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER

  Dose anybody have experience with mercator transformations and "false"
offsets like in my origin example,

   TIA, Wolfgang
postgis-users mailing list
postgis-users <at> lists.osgeo.org
Pierre Bails | 15 Jun 17:40 2016

Raster tile size impact results

Hi all,

I'm looking for get all pixel's value in a raster insert thanks to raster2pgsql intersect by a polygon. 
Nevertheless, there are some things that I don't understand: depends on tile size, I don't have the same result... The error between 2 raster is also correlate to the pixel size (I assume it's due to the pixel number).

Can you explain me how the tile size can influence the final result ?

Here is the query :
  FROM  mns INNER JOIN  st_setsrid(st_geomfromgeojson('{"type":"Polygon","coordinates":[[[x,y],[x,y],[x,y] ]]}'), 4326) as geom on ST_Intersects(mns.rast, geom)
) AS clip, ST_PixelAsCentroids (ST_Clip(clip.rast, clip.geom),1) AS px;

DSM px size 70cm: 
raster2pgsql -a -t 250x250 -s 4326 -F pathToDSM.tiff dsmTable (result: 46 347 846)
raster2pgsql -a -t 50x50 -s 4326 -F pathToDSM.tiff dsmTable    : 46 382 873
Difference: 35027

DSM px size 20cm : 
raster2pgsql -a -t 250x250 -s 4326 -F pathToDSM.tiff dsmTable :  567 505 888 
raster2pgsql -a -t 50x50 -s 4326 -F pathToDSM.tiff dsmTable    : 596 897 610
Difference: 29 391 722 
If we considere 250x250's result as reference, there is a difference of 0.07% in the first case, and 5% in the second. If the polygon area increases, the error rate increases.

Thank you,

postgis-users mailing list
postgis-users <at> lists.osgeo.org
Janovics Tamas | 15 Jun 02:53 2016

useful info


As I understood from your last message you were looking for some information , I think this is it http://lterantisa.tribemarket.com/aevzbty

Typos courtesy of my iPhone, Janovics Tamas

postgis-users mailing list
postgis-users <at> lists.osgeo.org
Birgit Laggner | 14 Jun 14:52 2016

ST_Split with Multilinestring

Dear list,

I would like to use the ST_Split function to split polygons from one table with all intersecting lines from another table. Unfortunately, I get the following error:

ERROR: Splitting a Polygon by a MultiLineString is unsupported
SQL Status:XX000

This is my query:

select betr_id, (cut).path[1], (cut).geom from (select betr_id, st_dump(st_split) as cut from (select a.betr_id, ST_Split(a.geom, st_union(b.geom)) from p_sam.nihb_2013_convex_hull_betr a left join p_sam.ni_dlm13_aaa_gew_sie_ver_l b on st_intersects(a.geom, b.geom) group by a.betr_id, a.geom) sel1) sel2;

The Manual contains the following info regarding this problem:

"The function supports splitting a line by (multi)point, (multi)line or (multi)polygon boundary, a (multi)polygon by line."

This might mean that I would be able to split a line by multilinestrings, but a polygon only by single linestrings - is that correct? Does anyone has a suggestion how I could work around this problem (aside from looping the ST_Split with some function, DO block or pgScript after selecting all intersecting linestrings per polygon)?

Thanks a lot for any helpful suggestions!



postgis-users mailing list
postgis-users <at> lists.osgeo.org
Michal Seidl | 13 Jun 17:08 2016

Performance external loop, WHERE IN (values), stored procedure or else to pass data to SQL query

I have probably simple question but I am stuck. I have table with about 1E6
points in table. I have about hundreds of points in Python array. How to
pass these points into Postgis SQL query to check against table (if the same
point exist) with millions points?

I started with Python loop to check every point individually but it is slow.
I have found lots of receipts how to fight with large data but they have to
be already in database.

Which way is good for this situation?
- create temporary table and firstly insert points into DB
- try to manage Python not to close connection and send SQL query with each
point fast
- try to push all points in one query? How?
- write stored procedure that will accept array of data?
- anything else?

May be it is more general SQL questions, but i am working with points.

Thanks for advice Michal

View this message in context: http://postgis.17.x6.nabble.com/Performance-external-loop-WHERE-IN-values-stored-procedure-or-else-to-pass-data-to-SQL-query-tp5010138.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
postgis-users mailing list
postgis-users <at> lists.osgeo.org