[postgis-users] intersecting and exporting

Francois Hugues hugues.francois at irstea.fr
Fri Nov 9 06:21:23 PST 2012

The right use of st_intersection imply the use of st_intersects in the where clause to avoid unuseful empty geometry collection.

Here is how I would have write your query :

select gid, st_intersection(a.geom, b.geom) as geom
from gshhs_c_l1 as a, regions as b
Where gid=4 and id=1 and st_intersects(a.geom, b.geom)

Could you try this and tell us if it's work please ?



-----Message d'origine-----
De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Seb
Envoyé : vendredi 9 novembre 2012 14:48
À : postgis-users at postgis.refractions.net
Objet : Re: [postgis-users] intersecting and exporting

On Fri, 9 Nov 2012 08:34:07 +0100,
"Francois Hugues" <hugues.francois at irstea.fr> wrote:

> Hello, To plot this in a gis you don't really need to export it you 
> can add a query layer in most of the gis software : - openjump :
> layer/sql query - qgis : layer/new postgis layer - arcgis : file/add 
> data/add query layer

> You can directly use your intersection query or select * from subset1

Thanks, I investigated qgis right after posting the question, and understood that this is exactly what "add PostGIS layer" does.  However, I cannot load these intersection queries that I created as views.  Only the imported GSHHS multipolygons (e.g. gshhs_c_l1) actually load.  Just after connecting to the database, the "type" and "geometry column"
columns for these intersection views in the "add PostGIS table(s)"
dialog have been showing "waiting" for the last 5 hours, so can't actually add them.  Additionally, the postgres process is pegged at 25% of my CPU since then.  If I drop the generated views, then nothing shows "waiting" in the dialog, and can load the imported GSHHS world coastline fine...  The intersections represent a very small subset of one of the polygons (a single object), so I am suprised.


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

More information about the postgis-users mailing list