[postgis-users] SRID, misc queries

Brian Hamlin maplabs at light42.com
Sat Oct 13 11:50:22 PDT 2007

Hella All-
   I am new to PostGIS, and have slowly been making some headway...
I have created a database that contains two tables, a set of Points, and
a set of MULTIPOLYGON. Following the directions, and with some false 
I have a geometry_columns table with two entries, one for a field in 
table, both set to SRID 4326.

   It is confusing to me when the geomtery_columns table is automatically
referenced, and when it is not. (I have had endless 'Operation on mixed
SRID geometries' errors)  For example, this query works

select count(*) from just_ca where st_within( 'POINT(-122.27 37.87)', 

   however, this one does not

  select count(*) from gectable where
st_within( geom_pts2,
'POLYGON(( 39.0 -122.0, 38.0 -122.0, 38.0 -121.0, 39.0 -121.0, 39.0 
-122.0))' )

... mixed SRID error

So, I tried casts, then SetSRID. (the syntax of casts is not 
well-explained as far as I
have found, nor the types, for example ::BOX3D or BOX2D)

So here is a current query

SELECT gectable.name
FROM just_ca, gectable
         just_ca.city = 'Berkeley'
  st_within( SetSRID(gectable.geom_pts2,4326),
   SetSRID( just_ca.the_geom,4326))

this takes 10 seconds on 110,000 points (and 12 polys in city Berkeley).
(I added a GIST index on geom_pts2 and the_geom, and the query took
the same amount of time as without indexes.  just_ca.city = 'Berkeley' 
takes just 4ms)

The following query gets a mixed SRID error

SELECT gectable.name
FROM just_ca, gectable
         just_ca.city = 'Berkeley'
  st_within(  gectable.geom_pts2, just_ca.the_geom )


More information about the postgis-users mailing list