[postgis-users] distance from imported tiger database

[Obe, Regina]

 Expand - expands the bounding box of the geometry x metrics in all
directions (in this case 100 meters).  This will work for all
geometries.  The general principle is that doing a bounding box overlaps
query && is a much faster operation (especially if you have indexed
geometries) than doing a distance (which is really min distance)
function which never takes advantage of indexes.  So it limits the list
of records your distance function needs to process since they would be
thrown out by your expand && compare.  You still need a distance call
because all geometries within 0 distance of each other have overlapping
bounding boxes but not all geometries that have overlapping bounding
boxes are within 0 distance of each other.

I think you lose a little since in this case I am transforming so I
suspect indexes are not used in this case unless you have an index on
transform(wkb_geometry, 26986), but I think its still faster than doing
a mere distance call.  Someone correct me if I am wrong - I actually
haven't tested that theory out.

Thanks,
Regina

-----Original Message-----
From: mark [mailto:rkmr.em at gmail.com] 
Sent: Saturday, February 17, 2007 10:31 PM
To: PostGIS Users Discussion
Cc: Obe, Regina
Subject: Re: [postgis-users] distance from imported tiger database

Hi Regina,

Can you pl. explain this query that you have given? I am not sure I
understand. What does expand do?

And does wkb_geometry have to be a line geometry for this to work?
What if wkb_geometry is a point geometry?
thanks a lot!
mark

On 2/12/07, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
>
>
> Slight correction.  I think its more efficient to write it like this
so that
> expand is only called once.
>
>
> SELECT fename, fetype, fedirps
> FROM masuf
> WHERE expand(transform(geometryfromtext('POINT(-71.10668
> 42.27081)', 4269), 26986),100) && transform(wkb_geometry, 26986) AND
> distance(transform(wkb_geometry, 26986),
> transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986))
<=
> 100
>
>  ________________________________
>  From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of Obe, Regina
> Sent: Monday, February 12, 2007 9:30 AM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] distance from imported tiger database
>
>
>
> Hi Mark,
>
> What does your location data look like.  Are you for example trying to
> compare distance of point locations from a street?
>
> For example the below will give you a listing of all streets that are
within
> 100 meters of the POINT(-71.10668 42.27081)  - note point is in long,
lat.
>
> The SRID 26986 you'll need to change to the SRID most suitable for
your
> particular location  which you can look up in the spatial_ref_sys .
> Basically you want the measurement to be units you want to measure by
and to
> be suitable for the region you are looking at.  For this example, I
> transformed my geometries to NAD 83 Mass State Plane meters (26986).
Items
> you will want to replace are in bold.  In general I find it useful to
store
> the geometry field in the coordinate system you will be using mostly
to
> minimize on having to write messy transform calls and to improve on
speed.
>
> SELECT fename, fetype, fedirps
> FROM masuf
> WHERE expand(transform(wkb_geometry, 26986), 100) &&
> transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986)
>     AND distance(transform(wkb_geometry, 26986),
> transform(geometryfromtext('POINT(-71.10668 42.27081)', 4269), 26986))
<=
> 100
>
> Hope that helps,
> Regina
>
>
>
>  ________________________________
>  From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of mark
> Sent: Sunday, February 11, 2007 9:03 PM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] distance from imported tiger database
>
>
> Hi
> I imported the tiger database with the ogr tool. It has created table
with
> the structure below.
> geometry_columns has an entry like this:
>
> 197276;"''";"public";"masuf";"wkb_geometry";"2";"4269";"LINESTRING"
>
> Can you tell how I can query for locations based on distance using
these?
> thanks a lot
> mark
>
> CREATE TABLE masuf
> (
> ogc_fid serial NOT NULL,
> wkb_geometry geometry,
> module char(8),
> tlid numeric(10),
> side1 numeric(1),
> source char(1),
> fedirp char(2),
> fename char(30),
> fetype char(4),
> fedirs char(2),
> cfcc char(3),
> fraddl char(11),
> toaddl char(11),
> fraddr char(11),
> toaddr char(11),
> friaddl char(1),
> toiaddl char(1),
> friaddr char(1),
> toiaddr char(1),
> zipl numeric(5),
> zipr numeric(5),
> aianhhfpl numeric(5),
> aianhhfpr numeric(5),
> aihhtlil char(1),
> aihhtlir char(1),
> census1 char(1),
> census2 char(1),
> statel numeric(2),
> stater numeric(2),
> countyl numeric(3),
> countyr numeric(3),
> cousubl numeric(5),
> cousubr numeric(5),
> submcdl numeric(5),
> submcdr numeric(5),
> placel numeric(5),
> placer numeric(5),
> tractl numeric(6),
> tractr numeric(6),
> blockl numeric(4),
> blockr numeric(4),
> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),
> CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),
> CONSTRAINT enforce_geotype_wkb_geometry CHECK
(geometrytype(wkb_geometry) =
> 'LINESTRING'::text OR wkb_geometry IS NULL),
> CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)
> )
>
>
>
> On 2/11/07, mark <rkmr.em at gmail.com> wrote:
> > hi
> > are there any tools for importing the tiger database into postgres
to use
> with postgis?
> > thanks !
> > mark
> >
>
>
>
>  ________________________________
>
>
>
>
>
>
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended solely for the
> addressee. If you received this in error, please contact the sender
> and delete the material from any computer.
>
>
>
>
>
> ________________________________
>
>
>
>
>
>
>  The substance of this message, including any attachments, may be
>  confidential, legally privileged and/or exempt from disclosure
>  pursuant to Massachusetts law. It is intended solely for the
>  addressee. If you received this in error, please contact the sender
>  and delete the material from any computer.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>