[postgis-users] distance from imported tiger database

mark rkmr.em at gmail.com
Sat Feb 17 19:30:30 PST 2007


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
>
>



More information about the postgis-users mailing list