[postgis-users] Problem with Intersection

[First Last]

Somebody please explain to me why the query below is taking too long. I mean aside from the fact that I am working on large dataset.

"SELECT * from (SELECT b.code, a.*, intersection(a.the_geom, b.memgeomunion) FROM rlrd AS a, (SELECT code, memgeomunion(the_geom) FROM countries WHERE code = 'CAN' GROUP BY code) AS b WHERE a.the_geom && b.memgeomunion AND intersects(a.the_geom, b.memgeomunion)) AS c where geometrytype(c.intersection)!='GEOMETRYCOLLECTION';"

This query is supposed to extract the railroad network of Canada. I am passing this query to psql to create a shapefile directly. It works with other countries but with Canada it's taking forever to finish (an hour has passed and still doesn't output anything, other take only about 3 mins). If it would help to know, each country is one record entry in the database even if it has several islands/polygons. Is the above query not optimal?

Also I had this problem when trying to save the output of the intersection as a  shapefile. Apparently the output dataset has a mixed geometry type that prevents it from being saved as a shapefile. In theory, clipping/intersecting a polyline with a polygon should produce a polyline subset. However, upon checking the output, I noticed I got point, lines, and multilines. I added the filter "geometrytype(c.intersection)!='GEOMETRYCOLLECTION'" because of this. This however didn't solve the problem all through out as some of the discarded (GeometryCollection) records contain valid lines, but because they are not contained in a homogenous record they were ruled out. YOu may refer to the screen capture I made - http://202.123.56.182/intx.gif . Notice that in addition to the red lines there is a small blue line and a dot. The blue line and the dot were extracted as a single heterogenous record (i.e., GEOMETRYCOLLECTION) and therefore filtered out. The blue line is valid though, and should have been included with the valid red lines. Is this a bug in PostGIS-PostgreSQL? It will be additional work if I try to extract only the homogenous records then loop thru the heterogenous ones and extract the valid parts and append them to the homogenous records.





_____________________________________________________________
Check out Atenista.Net (www.atenista.net)- new design, regular content and additional services!