Using ST_Intersection

Question

Here's a quick summary about what I'm trying to do: I have 3 tables in Postgres, 'a' and 'b', each have a Polygon column, and 'c' has a Point column. What I'm trying to do here is to get the geometries intersections between 'a', 'b' and 'c', and to display such geometries on an OpenLayers vector layer.

I already know how to display any kind of geometry from a String in OpenLayers, but I'm having troubles with the PostGIS' ST_Intersection function, I'm doing this:

SELECT ST_Intersection(a.geom, b.geom) as inter from a, b;

where a.geom and b.geom are both the geometry columns, and I get this error message:

NOTICE: TopologyException: found non-noded intersection between 515172 2.14408e+06, 497067 2.13373e+06 and 501321 2.13546e+06, 471202 2.14843e+06 500621 2.13576e+06 ERROR: GEOS Intersection() threw an error!

Also I tried to express the resultant geometry as text using ST_AsText like this:

SELECT ST_AsText(ST_Intersection(a.geom, b.geom)) as inter from a, b;

but it send me this error message:

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I don't know what I'm doing wrong, I just want to get the Polygons' WKT to display it on OpenLayers, here's how I display a geometry from a WKT:

 var in_options = { 'internalProjection': new OpenLayers.Projection("EPSG:4326"), 'externalProjection': new OpenLayers.Projection("EPSG:4326") }; var fea= new OpenLayers.Format.WKT(in_options).read(data); //data is the string with the WKT vectorLayer.addFeatures([fea]); //this piece of code works great map.zoomToExtent(bounds);

I tried the next:

SELECT ST_Intersection(a.geom, b.geom) as intersect_ab FROM a INNER JOIN b ON ST_Intersection(a,b) WHERE ST_Overlaps(a.geom, b.geom) AND ST_isvalid(a.geom)='t' AND ST_isvalid(b.geom)='t';

but I get the next error message:

ERROR: Function st_intersection(a,b) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I added the isvalid to verify only valid polygons are being evaluated, but it's telling the error is in the ST_Intersection(a,b), both a, b and c have the same SRID so I'm really confused. I'm quite new with PostGIS.

underdark · Accepted Answer · 2011-07-21 06:25:37Z

10

My guess would be that it fails if the intersection returns NULL. So you should add a where clause checking if there actually is an intersection before you try to create the WKT.

answered Jul 21, 2011 at 6:25

underdark

85k22 gold badges238 silver badges419 bronze badges

I tried this: SELECT ST_Intersection(a.geom, b.geom) as intersect_ab FROM a INNER JOIN b ON ST_Intersection(a,b) WHERE ST_Overlaps(a.geom, b.geom) AND ST_isvalid(a.geom)='t' AND ST_isvalid(b.geom)='t'; but it returned the same error : **ERROR: Function st_intersection(a, b) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts. ** I'm really stuck at this one, if you can help me I will realy appreciate it.

Uriel
– Uriel

2011-07-21 17:52:29 +00:00
Commented Jul 21, 2011 at 17:52

Add a comment |

Francisco Puga · Accepted Answer · 2011-11-05 13:10:52Z

The clue is

ERROR: Function st_intersection(a,b) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts.

As the error message says you can't use st_intersection in that way. Summarizing the other answers you should use something like this:

SELECT ST_Intersection(a.geom, b.geom) as intersect_ab FROM a INNER JOIN b ON ST_Intersects(a.geom,b.geom) WHERE ST_isvalid(a.geom)='t' AND ST_isvalid(b.geom)='t';

AFAIK there is no sense to use st_overlaps and st_intersects in the same sentence as they are pretty similar.

Fabien Ancelin · Accepted Answer · 2011-07-21 00:18:13Z

I test between different layer of polygons and it failed if there is at least an invalid geometry in one of the layer. Did you check the validity of your polygons using ST_isvalid(the_geom)? It may be the key.

CaptDragon · Accepted Answer · 2011-07-21 14:25:02Z

Try something like this:

SELECT ST_Intersection(a.geom, b.geom) As intersect_ab FROM a INNER JOIN b ON ST_Intersection(a,b) WHERE ST_Overlaps(a.geom, b.geom) ;

Source

Uriel · Accepted Answer · 2011-07-21 19:30:50Z

I tried to exclude the invalid geometries but it didn't work, so in the end I had to delete every invalid geometry and then use this:

SELECT ST_AsText(ST_Intersection(a.geom, b.geom)) as intersect_ab FROM a,b WHERE ST_Overlaps(a.geom, b.geom) AND ST_isvalid(a.geom)='t' AND ST_isvalid(b.geom)='t';

As you can see I omitted the ST_Intersection(a,b) part, and this worked great, I'm kind of sad beacuse I couldn't find a way to exclude any invalid geometry from my select, anyway thanks to everyone for helping me out here.

PolyGeo · Accepted Answer · 2014-03-07 10:21:54Z

I had this problem once.

<pre>NOTICE: TopologyException: found non-noded intersection between xxx, xxxx and xxx, xxx ERROR: GEOS Intersection() threw an error!</pre>

I was able to resolve this error using this method.
- Use QGIS
- Add vector layer from your database
- Take the point from the error message and search for it in QGIS
"QuickWKT" (plugin) can be used to find it
- Then you will see the problem linestring
- Turn on edit mode
- Select "node tool" to show green node (node problem)
- Move node away from overlap node
- Save changes

Stack Exchange Network

Using ST_Intersection

6 Answers 6

Linked

Hot Network Questions

Using ST_Intersection

6 Answers 6

Linked

Related

Hot Network Questions