[postgis-users] latitude / longitude from wkb_geometry
mark
rkmr.em at gmail.com
Mon Feb 12 11:49:44 PST 2007
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
>
>
>
More information about the postgis-users
mailing list