[postgis-users] New to postgis...

[Stephen Woodbridge]

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?