[postgis-users] latitude / longitude from wkb_geometry
Obe, Regina
robe.dnd at cityofboston.gov
Tue Feb 13 04:51:14 PST 2007
I forget whether the tiger code creates the tables automatically for you
or not, but hopefully the below article will help you in some way.
http://www.bostongis.com/PrinterFriendly.aspx?content_id=30
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of mark
Sent: Monday, February 12, 2007 9:01 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
Hi Regina,
Can you tell how I can import the files from here:
http://www.census.gov/geo/www/gazetteer/places2k.html
into Postgres? Without these tiger_geocode is giving errors.
Thanks a lot!
mark
On 2/12/07, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
>
>
>
> Unfortunately I don't think there is any documentation to my knowledge
aside
> from the help comments in the .sql files.
>
> When I used it I was using it mostly against Parcel data so I used the
> normalize_address and other cleansing functions mostly. For what you
want
> to do I think you would be primarily interested in the
>
> interpolate_from_address functions.
>
> But since you are using against the tiger data, you may be able to use
all
> of it as is.
>
> To use it, you'll need to download the gazeeteer tables that have
place
> names, zips etc which I think you can get from here
>
> http://www.census.gov/geo/www/gazetteer/gazette.html
>
> Thanks,
> Regina
>
>
>
>
>
> ________________________________
> From: mark [mailto:rkmr.em at gmail.com]
> Sent: Mon 2/12/2007 2:49 PM
> To: PostGIS Users Discussion
> Cc: Obe, Regina
>
> Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
>
>
>
>
> Hi Regina,
> That is exactly what I want to do!
> Do you have examples of using the tiger geocoder / documentation that
> you can point me to ?
> Thanks a lot!
> mark
>
> On 2/12/07, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
> > Hi Mark,
> >
> > It sounds like you are trying to create a geocoder using tiger
street
> data. So given an address -- are you trying to get the point on the
line
> where the address sits?
> >
> > If so, you may want to take a look at this and see if you can use
some of
> it
> >
> >
> http://postgis.refractions.net/download/tiger_geocoder.tar.gz
> >
> > Hope that helps,
> > Regina
> >
> > ________________________________
> >
> > From: postgis-users-bounces at postgis.refractions.net on
> behalf of Pedro Doria Meunier
> > Sent: Mon 2/12/2007 2:32 PM
> > To: 'PostGIS Users Discussion'
> > Subject: RE: [postgis-users] latitude / longitude from wkb_geometry
> >
> >
> >
> > Hi Mark,
> >
> > You're welcome. This is the bread-and-butter of this list... ;-)
> >
> > I must admit that I'm not familiar with TIGER data since I've never
used
> > it...
> > And living in Madeira Island, Portugal doesn't add any immediate
need for
> it
> > too... :D
> > Anyway...
> > If I understood your question correctly here's the
> > Sql query:
> >
> > SELECT pointn(wkb_geometry,1) from masuf where
> > field_containing_street_name='the adress';
> >
> > There's, of course, other things to take into consideration...
> > Like there's almost certainly various rows containing the same
address
> value
> > for different towns...
> > So you should append a 'AND
> field_containing_town_name='town name' to the
> > above statement. Also finishing it with a 'LIMIT 1' is advisable...
> >
> > So the statement goes:
> > SELECT pointn(wkb_geometry,1) from masuf where
> > field_containing_street_name='the adress' AND
> > field_containing_town_name='town name' LIMIT 1;
> >
> > This, again, returns the first point for that linestring and thus a
POINT
> > object.
> >
> > ---
> > Pedro Doria Meunier
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of mark
> > Sent: segunda-feira, 12 de Fevereiro de 2007 19:14
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] latitude / longitude from wkb_geometry
> >
> > Hi Pedro,
> > Thanks a ton!!!
> > Will try all of this out!!!!
> >
> > My location data is a US street address. Can you tell me what is the
> > best way to get to a POINT from the US Street address? I am
> > considering searching the masuf table that I have which I have
> > populated with TIGER database . If I map the US street address to a
> > row I get the line geometry from which I can get approximage point
> > data.
> >
> > Thanks a lot
> > mark
> >
> >
> >
> > On 2/12/07, Pedro Doria Meunier <pdoria at netmadeira.com> wrote:
> > > Hi Mark
> > >
> > > The distance function takes two parameters: distance(geometry,
geometry)
> > >
> > > It'll give you values based on the geometry's SRID.
> > > (And you shouldn't mix two different SRIDs when calling the
function --
> I
> > > expect that an exception would be raised...)
> > >
> > > For SRID==4269 values are returned in ddd.dddd (degrees) (GEOGCS).
> > >
> > > Should you want to work in meters you must transform it to a
projected
> > > coordinate system.
> > >
> > > You can use the longitude to get the desired zone. Here's the
formula:
> > > utmzone = ((lon + 180) / 6) + 1
> > >
> > > Having obtained the desired utm zone the following sql statement
returns
> > the
> > > srid:
> > >
> > > SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS
84 /
> UTM
> > > zone $utm%' LIMIT 1;
> > > Note: $utm is a variable, in this case PHP is being used...
substitute
> > with
> > > the value found above
> > > Note1: the '%' is a metacharacter. In this case it 'validates'
anything
> > > following the utm...
> > >
> > > In your case the returned SRID would be 32651 (using -121.913666
as the
> > lon)
> > >
> > > All this ends in this sql statement:
> > > SELECT distance(transform(wkb_geometry,32651),
> > > transform(geometryfromtext('POINT(-121.913666
> 37.292952)', 4269),32651)
> > from
> > > masuf;
> > >
> > > You can then pick the returned value and convert it to miles,
> whatever...
> > >
> > > HTH,
> > > Pedro Doria Meunier.
> > >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net
> > > [mailto:postgis-users-bounces at postgis.refractions.net]
> On Behalf Of mark
> > > Sent: segunda-feira, 12 de Fevereiro de 2007 17:09
> > > To: PostGIS Users Discussion
> > > Subject: Re: [postgis-users] latitude / longitude from
wkb_geometry
> > >
> > > Hi Pedro,
> > > Oops! I missed the previous email! Thanks a lot!!!!
> > >
> > > Now if I want to use this point in distance calculation should I
need
> > > to convert it to geometry right? What SRID should I use? The SRID
for
> > > line string is 4269 and that is the only row I have in
> > > geometry_columns table.
> > >
> > > select geometryfromtext('POINT(-121.913666 37.292952)', SRID???)
> > > Can I use the same SRID?
> > > PostGIS rocsk!!!
> > > Thanks a lot!
> > > mark
> > >
> > > On 2/12/07, Pedro Doria Meunier <pdoria at netmadeira.com> wrote:
> > > > Hi Mark
> > > >
> > > > Please see the previous email (to this one).
> > > > pointn() starts at 1, not 0... ;-)
> > > >
> > > > So the sql statement should be:
> > > > SELECT x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from
masuf
> > > > where ogc_fid=62560;
> > > >
> > > > This returns the first point of the linestring object.
> > > >
> > > > Cheers,
> > > > Pedro.
> > > >
> > > > -----Original Message-----
> > > > From: postgis-users-bounces at postgis.refractions.net
> > > >
> [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of mark
> > > > Sent: segunda-feira, 12 de Fevereiro de 2007 16:42
> > > > To: PostGIS Users Discussion
> > > > Subject: Re: [postgis-users] latitude / longitude from
wkb_geometry
> > > >
> > > > Hi Pedro
> > > > Thanks for your replies.
> > > > I tried your SQL query on this row:
> > > >
> > > > SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from
masuf
> > > > where ogc_fid=62560;
> > > >
> > >
> >
>
"62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C6
3247
> > > >
> > >
> >
>
B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10"
;"";
> > > >
> > >
> >
>
"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"
9283
> > > >
> 0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"
> > > >
> > > > I just get Null values for x and y.
> > > >
> > > > Can you tell what is wrong?
> > > > thanks a lot!
> > > > mark
> > > >
> > > >
> > > > On 2/12/07, Pedro Doria Meunier <pdoria at netmadeira.com> wrote:
> > > > > Hi Mark
> > > > >
> > > > > Addendum to the previous post...
> > > > > I didn't notice that the geometry was LINESTRING :]
> > > > > So what we need here is to concatenate some functions:
> > > > >
> > > > > SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0))
from
> > masuf;
> > > > >
> > > > > This will give you the first coordinate of each line.
> > > > >
> > > > > Should you want to process all coordinates of each linestring
here's
> a
> > > > > little PHP that might help:
> > > > >
> > > > > // ------ cut here
> > > > > <?php
> > > > > $connection = pg_connect("host=yourhost port=5432
dbname=yourdb
> > > > > user=yourusername password=yourpassword");
> > > > > if (!$connection) {
> > > > > print("Connection to the database failed.");
> > > > > exit;
> > > > > }
> > > > > /*
> > > > > Get all the records from the table.
> > > > > We get the unique ogc_fid and the corresponding number of
points for
> > the
> > > > > linestring of this entry...
> > > > > */
> > > > > $sql="SELECT ogc_fid, numpoints(wkb_geometry) from masuf";
> > > > > $myresult=pg_exec($connection, $sql);
> > > > >
> > > > > for ($row=0; $row<pg_numrows($myresult); $row++) {
> > > > > $unique=pg_result($myresult,$row,0);
> > > > > $npoints==pg_result($myresult,$row,0);
> > > > > // now we process each point in this entry
> > > > > for ($point=0; $point<$npoints; $point++) {
> > > > > $sql= "SELECT x(pointn(wkb_geometry,$point)),
> > > > > y(pointn(wkb_geometry,$point)) FROM masuf WHERE
ogc_fid='$unique'";
> > > > > $presult=pg_exec($connection, $sql);
> > > > > $lon=pg_result($presult,0,0);
> > > > > $lat=pg_result($presult,0,1);
> > > > > /*
> > > > > Do whatever you wish with $lon, $lat....
> > > > > */
> > > > > }
> > > > > }
> > > > > ?>
> > > > > // ------ cut here
> > > > >
> > > > >
> > > > > If the gurus out there have a more efficient way to do this,
I'd be
> > more
> > > > > than interested in hearing about it! ;-)
> > > > >
> > > > > HTH,
> > > > > Pedro Doria Meunier.
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: postgis-users-bounces at postgis.refractions.net
> > > > >
> [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of
> > mark
> > > > > Sent: segunda-feira, 12 de Fevereiro de 2007 5:57
> > > > > To: postgis-users at postgis.refractions.net
> > > > > Subject: [postgis-users] latitude / longitude from
wkb_geometry
> > > > >
> > > > > how to get latitude and longitude from wkb_geometry?
> > > > > My table structure is given below
> > > > > thanks
> > > > > 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)
> > > > > )
> > > > > _______________________________________________
> > > > > postgis-users mailing list
> > > > > postgis-users at postgis.refractions.net
> > > > >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > > >
> > > > >
> > > > > _______________________________________________
> > > > > postgis-users mailing list
> > > > > postgis-users at postgis.refractions.net
> > > > >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > > >
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > >
> > > >
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
> >
> >
> > -----------------------------------------
> > 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
> >
> >
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
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