[postgis-users] Latitude and Longitude format in PostGIS

Thu Apr 12 08:00:16 PDT 2007

I think the MakeLine function might do the trick for you.

Something like

select makeline(b.the_geom) as theline,  b.trip 
FROM (SELECT the_geom, trip FROM coords ORDER BY trip, id) b  
group by b.trip

trip is just an arbitrary field I made up which you may not have.  For
example if you have points for a certain visit denoted by trip,  Then to
get a linestring path for each trip, you would do the above.

If you just did 
select makeline(b.the_geom) as theline,  b.trip 
FROM (SELECT the_geom FROM coords ORDER BY id) b  

then you would get a single record that collapses all your points into a
single linestring which could get pretty huge depending on how many
points you have.

The subselect is to force the ordering of the points since makeline will
plot the points in the order they appear in the result set.

Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Giuseppe Molinario
Sent: Thursday, April 12, 2007 10:04 AM
To: Brent Wood
Cc: PostGIS Users Discussion
Subject: Re: [postgis-users] Latitude and Longitude format in PostGIS

Brent, 

Thank you - We are using the Sql code you gave us and it is working
good. We've managed to automate the Lat/long coordinates from an
entering gprmc format email right to our Web-GIS service.

Our problem now, that we are dealing with, is the creation of a line
from the inserted points. Do we really have to go back and use the
linestring from text function? converting again the lat/long
coordinates? Or do can we just use a Postgis function that "Connects the
dots" from the postgis format (have a geom column) points that are
already in the DB? 

Thanks again for th help guys!

Giuseppe

On 4/12/07, Brent Wood <pcreso at pcreso.com > wrote: 

	--- Giuseppe Molinario <g.molinario at gmail.com > wrote:

	You can use (I think) the proj binary to convert the dddmm.mmm
to dd.dddd, but
	it may be easier to use an SQL with the data in Postgis tables
as numeric data.

	The reformatting of ddmm.mmmm to dd.ddddd is not technically
reprojecting.

	The following is one way to do this, by inserting your
ddmmm.mmmm values, then
	generating a point geometry from these with an update SQL.

	ie, along the lines of: 

	# create your table...
	create table coords
	  (id   serial primary key,
	   x    decimal(10,5),
	   y    decinal(10,5))

	load your XY data...
	insert into coords values (default, 16778.8888, 4234.6667 );

	# add a geometry column to the table
	select addgeometrycolumn('','coords','the_geom',4326,'POINT',2);

	#populate the geometry column
	update coords
	set
the_geom=geomfromtext('POINT('||floor(x/100)+((x/100-floor(x/100))/0.60)
||' 
	'||floor(y/100)+((y/100-floor(y/100))/0.60)||')',4326);

	select id, x, y, astext(the_geom) from coords;
	id |      x      |     y      |                  astext

----+-------------+------------+----------------------------------------
-- 
	  3 | 16778.88880 | 4234.66670 | POINT(168.314813333333
42.5777783333333)
	(1 row)

	note that floor as used above will not work with negative
coords, but you can
	still use a similar approach using strings, or ceil/int & a
where clause for 
	negative coords....

	If you are comfortable writing a postgres function to do this
conversion, that
	is also possible.

	Cheers,

	  Brent Wood

	> Hi Mark,
	>
	> Yes, that helps. I'll see if i missed the proj4 installation
along the way, 
	> or if, more probably i am simply feeding PostGIS lat/long data
that is not
	> in decimal degrees.
	>
	> I'll let you know if it doesn't work.
	>
	> Thanks,
	>
	> Giuseppe 
	>
	>
	>
	> On 4/11/07, Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk>
wrote:
	> >
	> > On Wed, 2007-04-11 at 13:58 +0200, Giuseppe Molinario wrote:

	> > > Hi everyone,
	> >
	> > Hi Guiseppe,
	> >
	> > > I have a question regarding the creation in PostGIS of a
table that
	> > > contains information coming from a NMEA GPRMC text string.
I get 
	> > > Lat/long coordinates which are then put into their
respective columns.
	> > > A geometry field is then created using the SRID # 4326
(WGS84 Datum -
	> > > Geographic).
	> > > 
	> > > 1. What format of Lat/long coordinates does PostGIS
accept? GPRMC has
	> > > the coordinate in this format: ddmm.mmmmm.
	> >
	> > PostGIS accepts coordinates in decimal degrees. 
	> >
	> > > 2. How do I project the points into WGS84UTM33N? My final
goal is to
	> > > overlay these points as a PostGIS layer on a webmap
(Mapserver based)
	> > > over other data I have which is in that projection. 
	> >
	> > The transform() function should be able to do what you need
here if you
	> > use a version of PostGIS that has been compiled with the
PROJ.4
	> > reprojection library.
	> > 
	> > > 3. Do you know if Mapserver will re-project on the fly in
my case?
	> > > ever? or do I have to take care of this in PostGIS?
	> >
	> > Yes. Mapserver can also reproject layers on-the-fly using
PROJ.4. There
	> > are plenty of examples on the Mapserver website at
	> > http://mapserver.gis.umn.edu.
	> >
	> >
	> > HTH,
	> >
	> > Mark.
	> >
	> >
	> > _______________________________________________
	> > 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070412/3e084710/attachment.html>