Eric,
A few questions and suggestions:
1. st_contains() mean wholey inside and not touching or cross the
boundary of the contains IIRC. Is this really what you want?
2. If you want st_intersects() the I would suggest using distance()=0.0
as it is faster, at least on old versions of PostGIS, I think the newer
versions do that under the hood anyway.
3. Some queries to try:
select count(*)
from gis_schema.jksn_cnty_buidings
where
the_geom && 'POLYGON ((-88.981 32.0393, -88.8616 32.0393,
-88.8616 32.4663, -88.981 32.4663, -88.981 32.0393))';
select count(*)
from gis_schema.jksn_cnty_buidings
where
st_distance(the_geom, 'POLYGON ((-88.981 32.0393, -88.8616 32.0393,
-88.8616 32.4663, -88.981 32.4663, -88.981 32.0393))')=0.0;
Obviously(?), both these conditions have to be true before st_contains()
can be true, if I'm not mistaken.
-Steve W
On 12/6/2010 10:41 PM, Eric Ladner wrote:
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?
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
