[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