[postgis-users] distance from imported tiger database

[Obe, Regina]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070212/0c2f8295/attachment.html>