[postgis-users] latitude / longitude from wkb_geometry
Obe, Regina
robe.dnd at cityofboston.gov
Mon Feb 12 12:04:12 PST 2007
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";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247
> > >
> >
> 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
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070212/11876963/attachment.html>
More information about the postgis-users
mailing list