[postgis-users] New to postgis...

Eric Ladner eric.ladner at gmail.com
Mon Dec 6 19:41:32 PST 2010


I just started using postgis in conjunction with OpenStreetMap and
I've been having a heck of a time gettnig a query to work.

I'm fairly well versed in SQL in general (Oracle DBA for 10+ years)
but I'm new to the spatial stuff.   I've got a bunch of plot line data
downloaded from the state's GIS system loaded into a postgis database
and I was trying to slice it up into manageable chunks to upload to
OSM.  In the table, I've got almost 130,000 distinct objects and I can
view them with queries (like below... I shortened up the MULTIPOLYGON
list for brevity..)


     select gid, ST_AsText(the_geom) from
gis_schema.jksn_cnty_buidings where gid = 99;

     gid |    st_astext
    -----+-----------------
      99 | MULTIPOLYGON(((-88.7426828988773
30.40751849384,-88.7427319811994
30.4075187592759,....-88.7426828988773 30.40751849384)))

The theory goes like this:

Find the extents of the data (xmax, ymax, xmin, ymin - this part works great),

    select min(ST_XMin(the_geom)) as "min_x", max(ST_XMax(the_geom))
as "max_x" from gis_schema.jksn_cnty_buidings ;
    select min(ST_YMin(the_geom)) as "min_y", max(ST_YMax(the_geom))
as "max_y" from gis_schema.jksn_cnty_buidings ;

Then partition that space into 16 or 25 sub-areas with a Perl script
that generates individual queries for the sub quadrants..  I planned
on moving them to additional numbered tables (to avoid dupes when
objects overlap the bounding polygon) then to handle each table as a
separate upload.  The examples below are just selects, though, all of
which return zero rows.

    select * from gis_schema.jksn_cnty_buidings where
_ST_Contains(the_geom,'POLYGON ((-88.981 32.0393, -88.8616 32.0393,
-88.8616 32.4663, -88.981 32.4663, -88.981 32.0393))');
    select * from gis_schema.jksn_cnty_buidings where
_ST_Contains(the_geom,'POLYGON ((-88.8616 32.0393, -88.7422 32.0393,
-88.7422 32.4663, -88.8616 32.4663, -88.8616 32.0393))');

There are thousands of polygons all across the area, and I've hand
verified a couple.  The above two queries should return about 2300
records each, but every time I run it, it returns zero.

I've tried several variants like (including _ST and regular ST functions)

    select *
    from gis_schema.jksn_cnty_buidings
    where
      the_geom && 'POLYGON ((-88.5034 32.0393, -88.384 32.0393,
-88.384 32.4663, -88.5034 32.4663, -88.5034 32.0393))' and
       _ST_Contains(the_geom,'POLYGON ((-88.5034 32.0393, -88.384
32.0393, -88.384 32.4663, -88.5034 32.4663, -88.5034 32.0393))');

And even

    select gid, objectid from gis_schema.jksn_cnty_buidings where
ST_Within(the_geom,ST_AsText(ST_MakeEnvelope(-90, 30, -86, 30,
4269)));

An ideas where I'm messing up here?
-- 
Eric Ladner



More information about the postgis-users mailing list