[postgis-users] latitude / longitude from wkb_geometry

[Pedro Doria Meunier]

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