Paul Ramsey | 8 Oct 15:24 2015

PostGIS 2.2.0 Released

PostGIS 2.2.0 is released! Over the last two years a number of
interesting new features have been added, such as:

* True nearest-neighbor searching for all geometry and geography types
* New volumetric geometry support, including ST_3DDifference,
ST_3DUnion and more
* Temporal data model support and functions like
ST_ClosestPointOfApproach to support temporal query
* Spatial clustering functions ST_ClusterIntersecting and ST_ClusterWithin
* Subdividing large geometries with ST_Subdivide
* Fast box clipping with ST_ClipByBox2D
* In-database raster processing with ST_Retile and ST_CreateOverview
* New high-speed native code address standardizer
* Visvalingam-Whyatt geometry simplification with ST_SimplifyVW
* Support for compressed “tiny well-known binary” format with

See the full list of changes in the news file:

Thanks to everyone who helped with testing and development over this cycle!

Team PostGIS
postgis-users mailing list
postgis-users <at>
(Continue reading)

Alessandro Pasotti | 7 Oct 09:18 2015

How to get an interpolated point on a linestring and make sure ST_Intersects returns True?

Hi all,

This is my first post on this list: a warm hello to everybody :)

I'm trying to get a point on a linestring such as ST_Intersects with the 
original linestring will return True but it is failing in some real life 
situations (postgres 9.3, postgis version: 2.1 USE_GEOS=1 USE_PROJ=1 

Is this due to rounding errors?
Is there another (possibly working) way to do that without using buffers?

This is my test case:

id serial NOT NULL,
the_geom geometry(LineString,27700) NOT NULL,
CONSTRAINT trenches_pkey PRIMARY KEY (id)

id serial NOT NULL,
the_geom geometry(Point,27700) NOT NULL,
CONSTRAINT closures_pkey PRIMARY KEY (id)

(Continue reading)

Stefan Keller | 6 Oct 22:43 2015

Aggregating CAD lines to a single polygon?


I got a CAD dataset of crossroads where - you won't believe it - every
single road marking was captured by a closed line! This means, that
there are about 10 marking outlines with a 0.5m gap which make up one
single crossroad on the street.

Now, what I need is just the center point for each crossroad.

My approach:
1. calculate a buffer of ~0.6m around each line,
2. dissolve overlapping geometries (=> (ST_Dump(ST_Union(geom))).geom ?),
3. calculate centroid of resulting polygon.

Any other ideas?

toni hernández | 6 Oct 16:48 2015

raster2pgsql wildcards on windows

Hi all,

How can I import several raster files from several folders with raster2pgsql on windows?

This is what I am trying unsuccessfully.

raster2pgsql -r d:\postgis_in_action\ch13_data_code\vietnam\dted\*\*.dt0 -s 4326 -F -t 50x50 -I vietelev > vietelev.sql

"Unable to read raster file"  is what I get.

Toni Hernández Vallès
Servei de Sistemes d'Informació Geogràfica i Teledetecció
Universitat de Girona
Pl. Ferrater Mora 1
17071 Girona
Tel +34 972 418 039 (7026 intern)
toni <at>

postgis-users mailing list
postgis-users <at>
Andrew Davis | 6 Oct 15:32 2015

How do I handle a ResultSet from a java request?

Hi all..

I'm sending my first question out so patience please.

I've got a working servlet on my server that will query for and get information back from my postgis DB.

Up until now I have been just parsing the results and sending back a json string.  I want to get a GeoJSON back from my db now.

I've been working through the information I found here: 

I can create the table with locations included and a geog value, and I can run the query below

SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type , ST_AsGeoJSON(lg.geog)::json As geometry , row_to_json((loc_id, loc_name)) As properties FROM locations As lg ) As f ) As fc;
which brings back a single line string and is in the correct format all in PGAdmin3.
I have been trying to duplicate this from my server with java and am stuck at the point where I have run the query and have a 'thing' (ResultSet) but am unsure as to how to handle it and then pass it back to the client.
Any help would be welcome.
postgis-users mailing list
postgis-users <at>
Robert Burgholzer | 5 Oct 14:35 2015

Survey: Anyone Doing Flood Mapping with PostGIS?

With recent flooding in the eastern US, I am wondering if anyone is doing this?  If so:
* Are you mapping risk or incidence or both?
* Are you using raster or vector to calculate flood risk/incidence?
* If vector, what types of shapes, TIN or other?
* What delivery method are you using, i.e., desktop analysis only, web-mapping, mapserver, etc.


Robert W. Burgholzer
 'Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity.'  - Charles Mingus
postgis-users mailing list
postgis-users <at>
Trang Nguyen | 1 Oct 07:27 2015

Finding points of intersection of a linestring with another multiolygon and linestring

Hi forum,


I have a LINESTRING represent the waypoints of a trip through road segments (LINESTRING) and zones (MULTIPOLYGON).

For each segment and zone, I would like to find out the points in the waypoints  which intersected the targeted zone/segment geometries. First and last intersection point into and exiting the zone or segment  would the sufficient as well.

I can use st_intersection to find the intersection each of waypoints to segment and zone geometries, but this returns a geometry that doesn’t necessarily contain the subset of intersecting points in the linestring. The query will be run on large datasets, so performance is an important consideration. GIST indexes exist on all the geometries joined (zone_geom, segment_geom and waypoints).






st_transform(st_intersection(segment_geom, waypoints),4326) seg_wp_intersection, -- need subset of points in waypoints that intersected with segment_geom

st_transform(st_intersection(zone_geom, waypoints),4326)  zone_wp_intersection,  -- need subset of  points in waypoints that intersected with zone_geom

trip_id, waypoints, zone_id, segment_id

from od1.v_trip_zone_segment


startts>=TIMESTAMP '2015-01-16T12:20:29.000Z' and startts<TIMESTAMP '2015-01-16T17:20:30.000Z' and endts<TIMESTAMP '2015-01-16T17:20:30.000Z' and zone_id in ('kansas_303','kansas_601','kansas_603','kansas_604','kansas_10','kansas_11','kansas_9','kansas_310','kansas_311','kansas_315','kansas_301','kansas_302','kansas_307','kansas_306','kansas_305','kansas_204','kansas_201','kansas_3','kansas_2','kansas_1') and segment_id in ('5062926','5062931','5062932','4062933','5055496','5063065','5062826','5062825','5062824','5062643','5062644','5062645','5062646','5055551','5062839','5062849','5062895','5063081') and segment_mapversion='1501'

and st_intersects(segment_geom, waypoints)

and st_intersects(szone_geom, waypoints)





postgis-users mailing list
postgis-users <at>

St_union with group by faster

Hi all, 

I have a table in postgis which contains 6304 polygons.

I need to make a view which union some of this polygon.

I write this request :

SELECT champ1, champ2, st_multi(st_union(geom))::geometry(MULTIPOLYGON, 2154) geom
FROM table
GROUP BY champ1, champ2, champ3, champ4, champ5

The request works 36 seconds and give me the results.

I have created some index on champ1 and champ2.

Have you a tip to get the request faster ?

Guillaume ARNAUD
Cellule SIGD
Direction de l'Informatique
Conseil Départemental de Tarn-et-Garonne 
postgis-users mailing list
postgis-users <at>
Dharshan Bharathur | 30 Sep 06:59 2015

[Question] Point returned in ST_LineLocatePoint and ST_ClosestPoint is not able to detect in ST_Contains

Hi all, Need experts advice.

This is the same question I have asked here.

I am using postgis's ST_LineLocatePoint to find out the closest point on a LineString to the given Point, and using  ST_LineInterpolatePoint to extract a Point from the returned float number.(as referred here)

This is my ST_LineLocatePoint Query
SELECT ST_AsText(ST_LineInterpolatePoint(foo.the_line, ST_LineLocatePoint(foo.the_line,ST_GeomFromText('POINT(12.962315 77.584841)')))) AS g FROM (SELECT ST_GeomFromText('LINESTRING(12.96145 77.58408,12.96219 77.58447,12.96302 77.58489,12.96316 77.58496,12.96348 77.58511)') AS the_line) AS foo;


g ------------------------------------------ POINT(12.9624389808159 77.5845959902924)

Which exactly lies on the linestring I have passed. Demonstration is displayed here.

query with St_Closest function also returns the same points.

But when I check whether this point lies in the same linestring using ST_Contains it always return false, even though the point lies within.

My ST_Contains Query:

SELECT ST_Contains(ST_GeomFromText('LINESTRING(12.96145 77.58408,12.96219 77.58447,12.96302 77.58489, 12.96316 77.58496, 12.96348 77.58511)'),ST_GeomFromText('POINT(12.9624389808159 77.5845959902924)'));



I am not getting where I am doing wrong, why I am getting false. Can anyone help me in this.

I am using Postgresql
: 9.4, postgis : 2.1

Thanks & Regards
postgis-users mailing list
postgis-users <at>
Kiriakos Georgiou | 29 Sep 12:17 2015

Is 2.1.8 stable?


We recently upgraded from 32bit PostgreSQL 9.3.4 + PostGIS 2.1.2 to 64bit PostgreSQL 9.4.4 + PostGIS
2.1.8. All the required packages for PostGIS were upgraded to their latest versions. This is on Solaris 10
with the gcc that ships by sun (v3.x)

All went well, except when we did regression testing we noticed  a complex spatial query that goes from
geometry to geography and back (with SRID changes along the way) was crashing PostgreSQL. We broke down
that code to simpler pieces and it worked, but later we found other code that worked before that would crash
the database backend process too.

Unfortunately our application is complex to the point that we haven't been able to distill the query down to
something simple that I can open a ticket with. All I have are core dumps and my hunch that downgrading
PostGIS might give some clues (if things go back to being stable.)

Any experiences with 2.1.8 with regards to stability that you can share? We haven't tested 2.1.5, I'll
update on how that goes.

Bistrais, Bob | 28 Sep 22:46 2015

Problem updating geometry in a record

I have been able to derive a geometry from a query.  I want to insert that geometry into a Multilinestring field.  I have some SQL code to do it.  When I run the code, the output say that the query was successful.  But I don’t see the geometry inserted into the table.  Here is the code:


update highschools_aroo set routegeom =

(SELECT ST_Union(geom) as mygeom from (SELECT pt.geom

                FROM pgr_dijkstra(

                                'SELECT gid AS id,

                                                sourceroute::integer as source,

                                                targetroute::integer as target,

                                                (feet / 5280)::double precision AS cost,

                                                feet::double precision as reverse_cost

                                                FROM ngrdsaroo2',2465,1713,false,true)

                                                as di

                                                JOIN ngrdsaroo2 pt on di.id2 = pt.gid) as foo)

                                                WHERE source_node = 2465 and target_node = 1713;


-As mentioned, this runs with no errors, but the geometry field is not updated.  Any suggestions?



postgis-users mailing list
postgis-users <at>