[postgis-devel] Re: [postgis-users] Help with GPS Coord in PostGIS
TECHER David
davidtecher at yahoo.fr
Thu May 11 14:12:19 PDT 2006
Hi Chris, Hi Tyler
Many thanks for your help!
I understand now!
Actually in my SQL I've forgot to use trunc() when I did (latitude/100)
and for longitude/100
So the correct SQL query is
update gps_coord set
the_geom=geometryfromtext('POINT('||trunc(longitude/100)+(longitude-1000)/60||'
'||trunc(latitude/100)::int+(latitude-5400)/60||')',4326) ;
Ok I see my GPS waypoints now! This is my first GPS Import and my first
viewing with MapServer! Great!
Many thanks for your help
P.S: Chris the MakeLine() function works well ;-).. yes I should use a
clause like GROUP BY but it is understable for my query...and my DATA
DATA "makeline from (select makeline(the_geom),1 as gid from gps_coord) as foo USING UNIQUE gid USING SRID=4326"
works too! Adding "1 as gid" is a idea I'm used to playing with! ;-)
I put the projection as U told me ;-) (EPSG 4326)
Thanks.
Tyler Mitchell a écrit :
>Hi David,
>I think you have your latitude (y) and longitude (x) mixed up in the database! Hopefully that is all that it is. It's a common problem because we say "latitude" first and use "x" first in EXTENT settings.
>
>Tyler
>
>----- Original Message -----
>From: TECHER David <davidtecher at yahoo.fr>
>Date: Thursday, May 11, 2006 11:58 am
>Subject: [postgis-users] Help with GPS Coord in PostGIS
>
>
>
>>Hi All
>>
>>I have a project where I need to import a GPS File (a simple txt file)
>>which contains think like:
>>
>>Fahrer
>>A,133840.00,5407.4270,N,01038.0983,E,1,07,1.26,00036,M,045,M,,,44Fahrer A,133850.00,5407.4266,N,01038.0988,E,1,07,1.26,00033,M,045,M,,,4C
>>Fahrer
>>A,133900.00,5407.4265,N,01038.0989,E,1,07,1.27,00032,M,045,M,,,4AFahrer A,133910.00,5407.4265,N,01038.0989,E,1,07,1.27,00032,M,045,M,,,4B
>>Fahrer
>>A,133920.00,5407.4265,N,01038.0989,E,1,07,1.27,00031,M,045,M,,,4BFahrer A,134500.00,5407.4274,N,01038.0978,E,1,07,1.31,00037,M,045,M,,,4D
>>
>>into a postgis table
>>
>>CREATE TABLE gps_coord(
>>"FAHRER" text ,
>>"UTC" text ,
>>latitude double precision ,
>>"N/S" text ,
>>longitude double precision ,
>>"E/W" text ,
>>"QUALITY" text ,
>>"#SATELLITES" text ,
>>"HDOP" text ,
>>"ANTENNA ALTITUDE" text ,
>>"M" text ,
>>"GEO SEPERATION" text ,
>>"M " text ,
>>"QUALITDATA" text ,
>>"DRS ID" text ,
>>"CHECKSUM" text
>>);
>>
>>then
>>
>>longitude and latitude are in
>>
>>
>> degrees/minutes
>>
>>
>>select addgeometrycolumn('gps_coord','the_geom',4326,'POINT',2);
>>
>>First of all I'm not really sure for the srid to use with 4326
>>
>>
>>
>>I have the formula in order to have the
>>
>>update gps_coord set
>>the_geom=geometryfromtext('POINT('||(latitude/100)::int+(latitude-
>>5400)/60||''||(longitude/100)::int+(longitude-1000)/60||')',4326) ;
>>
>>select x(the_geom),y(the_geom) from gps_coord limit 6;
>> x | y
>>------------------+------------------
>>54.1237833333333 | 10.6349716666667
>>54.1237766666667 | 10.63498
>> 54.123775 | 10.6349816666667
>> 54.123775 | 10.6349816666667
>> 54.123775 | 10.6349816666667
>> 54.12379 | 10.6349633333333
>>
>>What I want to know is that did I am doing something wrong? A mistake?
>>
>>With MapServer I got nothing on my picture...
>>
>>Thanks for help
>>
>>#===========================================================
>># DEBUT DE LA MAPFILE
>>#===========================================================
>>MAP
>># EXTENT 10.5956216 54.0569046 10.8584151 54.1410554
>>#EXTENT 54.0659713745117 10.6110792160034 54.1319885253906
>>11.8429574966431EXTENT 10.6110792160034 54.0659713745117
>>11.8429574966431 54.1319885253906
>> FONTSET "../etc/fonts.txt"
>> IMAGECOLOR 125 125 125
>> IMAGETYPE png
>> SYMBOLSET "../etc/symbols.sym"
>> SIZE 680 371
>> UNITS DD
>> STATUS ON
>> NAME "testgis"
>>
>>#===========================================================
>># - symbole dash -
>>#===========================================================
>>SYMBOL
>> NAME "dash"
>> TYPE ELLIPSE
>> POINTS 1 1 END
>> FILLED TRUE
>> STYLE 10 5 5 10 END
>>END
>>#===========================================================
>># - symbole line-vertical -
>>#===========================================================
>>SYMBOL
>> NAME "line-vertical"
>> TYPE vector
>> POINTS
>> 0.5 0
>> 0.5 1
>> END
>>END
>>#===========================================================
>># - symbole line-horizontal -
>>#===========================================================
>>SYMBOL
>> NAME "line-horizontal"
>> TYPE vector
>> POINTS
>> 0 0.5
>> 1 0.5
>> END
>>END
>>
>> QUERYMAP
>> COLOR 255 255 0
>> SIZE -1 -1
>> STATUS OFF
>> STYLE HILITE
>> END
>>#===========================================================
>># - WEB -
>>#===========================================================
>> WEB
>> IMAGEPATH "/var/www/tutorial/tutorial/tmp/"
>> IMAGEURL "/tutorial/tutorial/tmp/"
>> METADATA
>> END
>> QUERYFORMAT text/html
>> END
>>#======================================================================================================================================================
>># MakeLine() -
>>#===========================================================================================================================================================
>> LAYER
>> CONNECTION "user=david dbname=testgis host=localhost"
>> CONNECTIONTYPE POSTGIS
>> DATA "makeline from (select makeline(the_geom),1 as gid from
>>gps_coord) as foo USING UNIQUE gid USING SRID=4326"
>># LABELITEM "gid"
>> METADATA
>> END
>> NAME "buildings"
>> SIZEUNITS PIXELS
>> STATUS ON
>> TOLERANCE 0
>> TOLERANCEUNITS PIXELS
>> TYPE LINE
>> CLASS
>> METADATA
>> END
>> STYLE
>> ANGLE 360
>> OUTLINECOLOR 80 80 80
>> SIZE 10
>> SYMBOL "circle"
>> END
>> END
>> END
>>#===========================================================
>># FIN DE LA MAPFILE
>>#===========================================================
>>
>>END
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>___________________________________________________________________________
>>Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail
>>et son interface révolutionnaire.
>>http://fr.mail.yahoo.com
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>
>
>
___________________________________________________________________________
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
http://fr.mail.yahoo.com
More information about the postgis-devel
mailing list