Christopher Braune | 14 Sep 22:21 2014


Hello PostGIS-Users,

I want to select all closed lines in my dataset. I think the function 
ST_IsClosed is usefull for this task.

I tried the following SQL-Sentence:
SELECT *, (SELECT ST_IsClosed(lines.linestring) AS tof) FROM 
public.lines WHERE tof='TRUE';

I get this error-message:

ERROR:  column "tof" does not exist
LINE 3: ...s.linestring) AS tof) FROM public.lines WHERE tof='TRUE'...
********** Fehler **********

ERROR: column "tof" does not exist
SQL Status:42703

Can anybody help me? In general I want to know how I use the 
boolean-results (like ST_IsClosed) in nested queries.

Best regards,
(Continue reading)

datagonzo | 11 Sep 20:00 2014

ERROR: function pagc_normalize_address(unknown) does not exist

This error is odd because the function works fine from template database and
because the function shows up in the list of extensions for the database I'm
using. Additionally, the parse_addres() function and other postgis functions

View this message in context:
Sent from the PostGIS - User mailing list archive at
Paragon Corporation | 11 Sep 05:33 2014

PostGIS 2.1.4 release

The 2.1.4 release of PostGIS is now available.

The PostGIS development team is happy to release patch for PostGIS 2.1, the
2.1.4 release. As befits a patch release, the focus is on bugs, breakages,
and performance issues

Download source code here:

Html docs:  ,
PDF docs:


    #2745, Speedup ST_Simplify calls against points
    #2747, Support for GDAL 2.0
    #2749, Make rtpostgisupgrade20_21.sql ACID
    #2811, Do not specify index names when loading shapefiles/rasters
    #2829, Shortcut ST_Clip(raster) if geometry fully contains the raster
and no NODATA specified
    #2895, Raise cost of ST_ConvexHull(raster) to 300 for better query plans

Bug Fixes

    #2605, armel: _ST_Covers() returns true for point in hole
    #2911, Fix output scale on ST_Rescale/ST_Resample/ST_Resize of rasters
with scale 1/-1 and offset 0/0.
        Fix crash in ST_Union(raster)
(Continue reading)

Max Demars | 4 Sep 21:20 2014

ST_Difference query returns a dissolved result


I am using the ST_Difference to erase multipolygon feature by a mask. However, the query returns a dssolved multipolygon.

The query is the following:

'''SELECT ST_AsGeoJSON(ST_Collect(geom))
     ST_Dump (
             As f''' % (feature,mask)

Please take a look at those images above to understand the problem:

postgis-users mailing list
postgis-users <at>
DanielFranco | 4 Sep 17:05 2014

Exporting raster table into a file

I'm reclassifying a landsat image in PostGIS 2.0 using the ST_Reclass
function in a big raster (size about 50000x70000) and I'm trying to check
the results in the new raster table I created that contains the reclassified
raster. So, I decided to export the raster table to a file to check the
results, but gdal_translate is taking too much time to export (1 hour and
progress is in 40%). The command I'm using is: 

gdal_translate -of GTiff --config GDAL_CACHEMAX 512 "PG:host='localhost'
dbname='raster' user='postgres' password='postgres' schema='public'
table='mosaic' mode='2' " new_raster_directory.tif

Is there a way to export the whole image faster? It seems like the
"\lo_export thing" only exports tiles of the raster, but i'm not sure about
that. Is there an easier way to visualize the raster without exporting? I
heard that QGIS has an plugin for that, but someone can tell me that it is

View this message in context:
Sent from the PostGIS - User mailing list archive at
Max Demars | 2 Sep 19:19 2014

ST_AsGeoJson output geometry type not the same as input

Hi list,

I am using postgis to split a multipolygon feature by a linestring. Both feature are geojson inputs.

However the outputs returns a geojson that is a geocollection of two polygon features. In fact this is correct, but for consistency I would like to keep the the splitted features into a single Multipolygon (even if there is only one linear ring in it). Is it possible?

This is my query:

SQLQuery = '''SELECT ST_AsGeoJSON(ST_Split(ST_GeomFromGeoJSON('%s'),ST_GeomFromGeoJSON('%s')))::json As f''' % (feature,blade)

-Max Demars
postgis-users mailing list
postgis-users <at>
Ceyhun Ganioglu | 1 Sep 21:17 2014

Is caching PostGIS database possible


Is it possible to cache a whole PostGIS database? For example:

If a whole database is around 7 GB and the server has 24 GB average 
available RAM. Is it possible to cache all database to RAM and query the 
GIS records from RAM instead of disk?

Many thanks
Paragon Corporation | 1 Sep 14:39 2014

Re: [postgis-devel] Is there a reason we don't have an ST_Intersection aggregate function


 >  What I target is not the unioning between the layers but inside the same layer. The problem is the same -> to do something with more than two geometries involved.

 >  Adding aggregates is really less a priority than adding support for
ARRAY[geom1,geom2,geom3...] for the relevant functions.
> Are you sure that the planner and indexes will manage arrays better?
The reason I was thinking an array of aggregates is not as useful as an ST_Intersection aggregate is that as you intersect geometries you are using less memory rather than more
with array you'd have to accumulate them first.
It really is a sequential thing.
That is not to say the ARRAY version isn't useful, but if you have a bunch of geometries and then have to array them to get an intersection, then it would be slower.
I could be wrong on that of course. Paul and Nicklas can correct me on that since they are more familiar with the innards.
I have on occasion come into the situation where the things I want to get intersection of are in separate rows rather than separate layers and it is a tad bit annoying to work around it. Doesn't come up often though.
Now with 9.4 coming with the FILTER syntax now makes the across rows much easier if I actually had an ST_Intersection aggregate that could take advantage of it.
 >  Yes, I guess you are right that it would be possible to write a function that does the job from a collection

 >  But the function will have to do the same thing. To calculate a result from 2 polygons, then use the resulting polygon for calculation against the third polygon and so on. Just like an aggregate function works.
I actually hadn't thought of our ST_UnaryUnion  equivalent of ST_Intersection  which is what I think Nicklas is talking about here.  That would be useful as well.
That introduces another question though?
Would an ST_Intersection aggregate double as an ST_UnaryIntersection?
or we just keep them separate.
I would say keep them separate so that we have a parallel with ST_UnaryUnion and also you can use it NOT as an aggregate to satisfy the array like need.
postgis-users mailing list
postgis-users <at>
Paragon Corporation | 1 Sep 03:16 2014

Is there a reason we don't have an ST_Intersection aggregate function

I wasn't sure if we just thought there wasn't much utility in it or if there
was a technical obstacle.

I would think that would be easier to build than the other aggregates since
as you add more geometries it shrinks and the current aggregate state is
always the result of the previous state plus new geometry (so no need for an
accumulation function or a final state function) and if you ever end up with
a  geometry collection, you short-circuit out.

Max Demars | 28 Aug 16:55 2014

Get a feature geojson from postgis

Hi list,

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature.

so far I modified the feature collection query but the output is not valid:

    "SELECT row_to_json(fc) \
                            FROM (SELECT 'Feature' As type \
                                 , ST_AsGeoJSON(%s)::json As geometry  \
                                 , row_to_json((id_relat,id)) As properties \
                                FROM shapefile_feature WHERE id=%s)As fc;" % (geometryField,

invalid output:


Anyone can help here?
postgis-users mailing list
postgis-users <at>
Christopher Braune | 25 Aug 12:37 2014

boundingbox into vector matrix

Hello postgis users,

I have a bounding box and need homogeneous polygons (rectangles) like a 
matrix inside of the bounding box. The number (or size) of these 
polygons should be variable. Can anybody help me with an useful (SQL-) 

Best regards,