Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

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.

share|improve this question
1  
What does SELECT PostGIS_Full_Version(); return? –  Mike T Jul 21 '11 at 0:19
    
POSTGIS="1.4.0" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS –  Uriel Jul 21 '11 at 18:03
add comment

6 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
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 Jul 21 '11 at 17:52
    
Try summary(a.geom) and summary(b.geom) to investigate the values. –  underdark Jul 21 '11 at 17:58
    
summary -------------------------- Polygon[BS] with 1 rings ring 0 has 4 points Polygon[BS] with 1 rings ring 0 has 5 points Polygon[BS] with 1 rings ring 0 has 10 points –  Uriel Jul 21 '11 at 18:04
    
Yeah, it should be ST_Intersection(a.geom,b.geom) not ST_Intersection(a,b) –  underdark Jul 21 '11 at 18:40
add comment

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.

share|improve this answer
    
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 –  Uriel Jul 21 '11 at 17:43
add comment

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

share|improve this answer
    
I tried that too but it returns the same error message: HINT: No function matches the given name and argument types. You might need to add explicit type casts. –  Uriel Jul 21 '11 at 17:41
    
How about if you use "INNER JOIN b ON ST_Intersection(a.geom,b.geom)"? –  CaptDragon Jul 21 '11 at 17:51
    
It tells: ERROR: argument of JOIN/ON must be type boolean, not type geometry. –  Uriel Jul 21 '11 at 18:02
    
shizer... there must be something wrong with the data or something because this type of query works for me. –  CaptDragon Jul 21 '11 at 18:15
    
I added AND ST_isvalid(a.geom)='t' AND ST_isvalid(b.geom)='t'; at the end to only evaluate the valid geometries but it's telling me the error is in that st_intersection(a,b) –  Uriel Jul 21 '11 at 18:23
add comment

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.

share|improve this answer
    
Thanks! This did help. –  Stefan Aug 28 '12 at 7:54
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.