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:

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);

UPDATE: 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, sorry if I'm asking too much, but I'm quite new with PostGIS so I hope I'm not bothering you a lot. Thanks.

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.
–
UrielJul 21 '11 at 17:52

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.

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 lost as to why it is not working
–
UrielJul 21 '11 at 17:43

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.

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