Lars Aksel Opsahl | 28 Mar 16:47 2015

Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big performance decrease.


We have a data set with many millions of lines/polygons and we use the postgis topology package and it is
working quite well, but update is sometimes going slow.

The problem is related to abs(r.element_id) generated when the function

topology.ST_ModEdgeSplit(atopology varchar, anedge integer, apoint geometry) is called.

The sql generated is this :

“SELECT r.* FROM topo_ar5_sysdata.relation r, topology.layer l WHERE l.topology_id = 21 AND l.level =
0 AND l.layer_id = r.layer_id AND abs(r.element_id) = 30145 AND r.element_type = 2”

and it returns 1 row in 3173.108 ms

If I remove the abs function and use the r.element_id directly it sometimes go about 200 times faster.

SELECT r.* FROM topo_ar5_sysdata.relation r, topology.layer l WHERE l.topology_id = 21 AND l.level = 0
AND l.layer_id = r.layer_id AND r.element_id = 30145 AND r.element_type = 2


topogeo_id | layer_id | element_id | element_type


30148 | 1 | 30145 | 2

(1 row)
(Continue reading)

Igor Stassiy | 26 Mar 17:59 2015

Does ST_SimplifyPreserveTopology preserve "contains" property


does any postgis implementation of ST_SimplifyPreserveTopology preserve
"contains" property for polygons and multilines, that is, if I have a (Multi)Polygon Parent and a (Multi)Linestring/(Multi)Polygon Child s.t. 
ST_Contains(Parent, Child) holds, will ST_Contains(ST_SimplifyPreserveTopology(Parent), ST_SimplifyPreserveTopology(Child)) also hold?

Here I assume that the threshold for simplifying is the same for both Child and Parent.

Thank you,
postgis-users mailing list
postgis-users <at>
Stefano Iacovella | 25 Mar 12:51 2015

Issues configurign PostGIS with GDAL 1.11.2

Hi all,

sorry for the cross-posting, I am not sure whether the issue is more relevant to GDAL or PostGIS.

I tried to compile PostGIS 2.1.6 on my Linux Box but the first step end with an error:

~/sources/postgis-2.1.6 $ ./configure --with-gui
TOPOLOGY: Topology support requested
RASTER: Raster support requested
checking for gdal-config... /usr/local/bin/gdal-config
checking GDAL version... 1.11.2
checking for OGR enabled... yes
checking gdal.h usability... yes
checking gdal.h presence... yes
checking for gdal.h... yes
checking ogr_api.h usability... yes
checking ogr_api.h presence... yes
checking for ogr_api.h... yes
checking cpl_conv.h usability... yes
checking cpl_conv.h presence... yes
checking for cpl_conv.h... yes
checking for library containing GDALAllRegister... no
configure: error: could not find GDAL

It is the same Linux Box, Mint 17, on which I had previously successfully configured and compiled PostGIS 2.1.5 on GDAL 1.11.1  
The upgrade to GDAL 1.11.2 was done after the compilation of PostGIS 2.1.5 so I guess the problem may be related to this release.
I have in fact also tried to use the same configure command on sources for PostGIS 2.1.5 but got the same error.

Am I doing something wrong or is it a bug?

Thank you in advance for your help

41.95581N 12.52854E!/Iacovellas
postgis-users mailing list
postgis-users <at>
Andreas Laggner | 23 Mar 17:15 2015

raster2pgsql creates tables without values

Hi list,

all my rastermaps i import are empty, no values (NaN only).

Import on another machine with a different installation of PostGIS for 
this data works good.
I also tried to split the import, first create the sql-file and than 
import with psql - NO ERRORS accurs, but no values.

It must have to do with my installation of PostGIS or GDAL or something 
else. I don't know how to find/check this.

I have no idea - perhaps someone else out there has....

hasta luego        Andreas

Dipl. Geoökologe Andreas Laggner

Thünen-Institut für Agrarklimaschutz (AK)
Arbeitsgruppe Emissionsinventare
Johann Heinrich von Thünen-Institut (vTI),
Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

Thünen Institute of Climate-Smart Agriculture
Johann Heinrich von Thünen Institute (vTI),
Federal Research Institute for Rural Areas, Forestry and Fisheries

Bundesallee 50
D-38116 Braunschweig

Tel.: (+49) (0)531 596 2636
Fax : (+49) (0)531 596 2645
E-mail: andreas.laggner <at>

postgis-users mailing list
postgis-users <at>
Igor Stassiy | 23 Mar 15:16 2015

Difference between light-weight geometry types


is there any difference between LWCOLLECTION and LWCOMPOUND? Has the meaning changed since postgis 1.5 (when liblwgeom wasn't yet packaged) to liblwgeom >= 2.0? 

Also, what does LWCIRCSTRING stand for? Is it a closed line string?

Thanks a lot,
postgis-users mailing list
postgis-users <at>
Aaron Lewis | 22 Mar 12:09 2015

Need help on basic concepts, I just need really simple calculations


I've been searching online for days. Trying to understand why SRID is
required. So I picked some random value.

Now I'm need to retrieve POINTs within a circle range, e.g a circle at
(146.0, 138.19) with radius of 100 meters:

WHERE ST_DWithin (users.location, st_setsrid(st_makepoint (146.0,
138.19), 2600), 100);

It's very simple, but the result seems wrong. I have a record contains
a POINT(55 43) that matches this query.

Anyone know what's wrong with it?


Best Regards,
Aaron Lewis - PGP: 0x13714D33 -
Finger Print:   9F67 391B B770 8FF6 99DC  D92D 87F6 2602 1371 4D33
Paul Ramsey | 20 Mar 18:45 2015

2.1.6 Released

The 2.1.6 release of PostGIS is now available.

The PostGIS development team is happy to release patch for PostGIS 2.1, the 2.1.6 release. As befits a patch release, the focus is on bugs, breakages, and performance issues. Users with large tables of points will want to priorize this patch, for substantial (~50%) disk space savings.


  • #3000, Ensure edge splitting and healing algorithms use indexes
  • #3048, Speed up geometry simplification (J.Santana <at> CartoDB)
  • #3050, Speep up geometry type reading (J.Santana <at> CartoDB)

Bug Fixes

  • #2941, allow geography columns with SRID other than 4326
  • #3069, small objects getting inappropriately fluffed up w/ boxes
  • #3068, Have postgis_typmod_dims return NULL for unconstrained dims
  • #3061, Allow duplicate points in JSON, GML, GML ST_GeomFrom* functions
  • #3058, Fix ND-GiST picksplit method to split on the best plane
  • #3052, Make operators <-> and <#> available for PostgreSQL < 9.1
  • #3045, Fix dimensionality confusion in &&& operator
  • #3016, Allow unregistering layers of corrupted topologies
  • #3015, Avoid exceptions from TopologySummary
  • #3020, ST_AddBand out-db bug where height using width value
  • #3031, Allow restore of Geometry(Point) tables dumped with empties in them

View all closed tickets.

Paul Ramsey
postgis-users mailing list
postgis-users <at>
Mark Wynter | 19 Mar 22:17 2015

Re: Problem importing SHP polygons

If Mark's suggestion doesn't fix it... 

sounds like the imported geometries in PostGIS are not valid - possibly due to self intersections and on
occasions polygons with zero areas that appear like linestrings - something not uncommon when
converting rasters to vectors.

run some checks on your imported geometries, like
st_isvalid, st_area, geometrytype.

Depending on what you find, you can develop a set of cleaning steps like

St_makevalid, st_buffer(,0),
where geometrytype in(multi polygon,polygon) etc.

> Message: 5
> Date: Thu, 19 Mar 2015 09:40:07 +0000
> From: Mark Cave-Ayland <mark.cave-ayland <at>>
> To: Conrad Bielski <conrad_bielski <at>>,    PostGIS Users
>    Discussion <postgis-users <at>>
> Subject: Re: [postgis-users] Problem importing SHP polygons
> Message-ID: <550A9977.3000400 <at>>
> Content-Type: text/plain; charset=windows-1252
>> On 10/03/15 14:48, Conrad Bielski wrote:
>> Hi,
>> I'm having a curious issue importing a polygon SHP file into PostGIS.
>> I'll describe the procedure that I'm using and the problem that I'm
>> having and hopefully somebody has had this issue in the past and fixed it.
>> So the procedure is that I use GRASS to produce a vector from a raster
>> output: ( input=MASK output=vecMap feature=area)  and then
>> export to SHP file (v.out.ogr -c -e input=vecMap dsn=fname
>> format=ESRI_Shapefile type=area).
>> Now when this SHP file is imported into PostGIS, the type is changed to
>> multistring for some reason. Then when checking the topology, there is a
>> 'ring self-intersection' error. This PostGIS db is then imported into
>> GeoServer, where lines are connecting between different parts of the
>> largest polygon (this should not be happening). If I fix this typology
>> problem, then the largest polygon in the db disappears.
>> The strange thing is the following:
>> 1. Opening the SHP file in QGIS doesn't have this problem
>> 2. Importing the SHP file directly into GeoServer, doesn't generate this
>> problem either.
>> Why is PostGIS having an issue importing this file?
>> Any ideas how to fix this?
>> Thanks in advance for your help,
>> Conrad
> Hi Conrad,
> Have you tried disabling the MULTI conversion code in shp2pgsql (add -S
> option to the command line) to see if that helps
Ivor van Rensburg (TMG | 19 Mar 17:43 2015




I am looking for an app where I can graphically select an area on a map that comprises various regions (provinces / states) and the app will return a list of all regions (provinces / states), cities and towns in that specific area.


Please see the attached example.


Could you please assist.


Many thanks and kind regards


Ivor van Rensburg

The Media Gateway

Mobile: +27 82 652 8050

Facsimile: +27 86 512 6940

Email: info <at>

Skype: ivor.van.rensburg

postgis-users mailing list
postgis-users <at>
dave.potts | 19 Mar 09:10 2015

Finding the min point point along an line and ST_Line_Substring

I have a line string  geometry,  I am trying to find the min point between
the start and end point of this line

Can I use st_Line_substring(foo,0.5,0.5) to return the min point ?

Are there any pit falls I should be aware of ?
Ahmet Temiz | 18 Mar 19:55 2015

wrong distance calculation


I am trying to calculate the distance from the schools to active faults and their lengths.
 But then I tried to validate the some results and noticed that spatial query gave
longer distances than checked values (in QGIS).

I used this sql fragment:

SELECT sOk.adi as adi,sFay.gid as faygid,sOk.gid as OkGid,sOk.bolgeno,sFay.fayadi,,
              ST_Length(geography(sFay.the_geom))::numeric(10,2) as FayBoyu , sOk.the_geom as the_geom ,
              ST_Distance(                                         *****
              ) ::numeric(10,2) AS distance 
        FROM sOkul_DepZon as sOk 
        JOIN sdirifay_4326 as sFay
        ON ST_DWithin(
              120000) and  (sFay.faytipi~'1' or sFay.faytipi~'2')


​Can you tell me what the likely mistakes are ?

Ahmet Temiz


Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Planlama ve Zarar Azaltma Dairesi Başkanlığı


Ahmet Temiz
Geological Eng.
Information Systems - GIS Group
Disaster and Emergency Management
of Presidency
postgis-users mailing list
postgis-users <at>