PostGIS Spatial Database Engine UMN Mapserver Boston Geographic Information Systems    Checkout our PostGIS in Action book.  First chapter is a free download   PostGreSQL Object Relational Database Management System
GIS Books  Home   Consulting Services  About Boston GIS   Boston GIS Blog  Postgres OnLine Journal
PostGIS in Action is out in hard-copy,
download the first chapter
and SQL Primer for free. Tips and Tricks for PostGIS
  GIS Article comments Comments Rss
Intersects Intersection: PostGIS - ST_Intersects, ST_Intersection

ST_Intersects, Intersects

ST_Intersects is a function that takes two geometries and returns true if any part of those geometries is shared between the 2. In PostGIS versions before 1.3 you would use the following syntax to utilize indexes

SELECT a.somfield, b.somefield2
FROM a INNER JOIN b ON 
	(a.the_geom && b.the_geom AND intersects(a.the_geom, b.the_geom))
In versions from 1.3 forward, the && indexable operator is automatically included in the definition of ST_Intersects so you can simply write
SELECT a.somfield, b.somefield2
FROM a INNER JOIN b ON ST_Intersects(a.the_geom, b.the_geom)

ST_Intersection, Intersection

The functions ST_Intersection and Intersection are compliments to ST_Intersects. What they return is that portion of geometry A and geometry B that is shared between the two geometries. If the two geometries do not intersect, then what is returned is an empty GEOMETRYCOLLECTION object.

NOTE: PostGIS versions 1.2.2 and up you should use ST_Intersection as Intersection is deprecated. The two functions are more or less equivalent though.

Pictorial View of ST_Intersection

In this section we provide a graphical representation of what the Intersection looks like. We will use an example of a building and a parcel where the building does not completely sit inside the parcel.

SELECT b.the_geom As bgeom, p.the_geom As pgeom, 
		ST_Intersection(b.the_geom, p.the_geom) As intersect_bp
	FROM buildings b INNER JOIN parcels p ON ST_Intersection(b,p)
	WHERE ST_Overlaps(b.the_geom, p.the_geom)
	LIMIT 1;
Parcel Geometry: pgeom:
parcel
Building Geometry Overlayed on Top Of Parcel: bgeom: The building is in green
building
The geometry returned by ST_Intersection(b.the_geom, p.the_geom) overlayed on top of Parcel: (intersection is in brown)
intersection


Post Comments About Intersects Intersection: PostGIS - ST_Intersects, ST_Intersection




 CommenterComment
5/10/2009 4:15:16 PMLeoBen -- when you say falling over -- what sort of error are you getting?
5/5/2009 1:01:13 AMBenHi Regina,

I created two layers in QGIS that had an overlap similar to your diagrams, then I imported them into two separate tables and then ran your sql verbatim. I think it kept falling over at the ST_Intersection(b,p).

Ben
4/5/2009 1:58:31 AMReginaBen,

Are you using the table name or the geometry name. Here is a simple exercise

SELECT ST_Intersection(ST_GeomFromText('LINESTRING(1 2, 3 4)'), ST_GeomFromText('POINT(1 2)'));

If you are using a table -- you statement should look like
SELECt ST_Intersection(table1.the_geom, table2.the_geom) As theint
FROM table1 INNER JOIN table2 ON ST_Intersects(table1.the_geom, table2.the_geom)
4/1/2009 8:49:43 PMben daviesHello,
I have attempted to run your example using PostgreSQL 8.3 and PostGIS 1.3.5 and get the following error ...

ERROR: function st_intersection(table_1, table_2) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Would you be able to tell me what the possible cause of this is.

Thank You
Ben

This Document is available under the GNU Free Documentation License 1.2 http://www.gnu.org/copyleft/fdl.html & for download at the BostonGIS site http://www.bostongis.com

Boston GIS      Copyright 2014      Paragon Corporation