lz7cjc
asked on
Storing coordinated in Postgresql
Hi
I have got a dataset that includes Long/latitude and Easting/Northing; a few of the records look like this:
51.750365 -0.34032 514667 207044
51.563842 0.207556 553111 187305
51.69484 -0.386208 511636 200798
51.393646 -0.171873 527286 167653
51.42931 -0.500216 504362 171101
51.413846 -0.422078 509831 169495
51.660042 -0.405021 510420 196899
51.680993 0.009067 538996 199936
I have read that I should store these values as "points" in Postgresql but when I try to import from my excel spreadsheet where they are stored as "general", I get an error. I have also tried to store as a number and that didn't work either.
What is the best way to do this?
thanks
I have got a dataset that includes Long/latitude and Easting/Northing; a few of the records look like this:
51.750365 -0.34032 514667 207044
51.563842 0.207556 553111 187305
51.69484 -0.386208 511636 200798
51.393646 -0.171873 527286 167653
51.42931 -0.500216 504362 171101
51.413846 -0.422078 509831 169495
51.660042 -0.405021 510420 196899
51.680993 0.009067 538996 199936
I have read that I should store these values as "points" in Postgresql but when I try to import from my excel spreadsheet where they are stored as "general", I get an error. I have also tried to store as a number and that didn't work either.
What is the best way to do this?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks - I agree but I can't work out how to use PostGIS! I have installed it but I can't find it (
It is normal SQL http://postgis.net/docs/ST_MakePoint.html
ASKER
ok but if I am using the visual editor, pgadmin, where do I find that option in the table builder ->add column?
It has extensions to default postgresql installation, like dll/so files.
you can also configure your own type of two floats, or plainly use 2 floats to record coordinates.
you can also configure your own type of two floats, or plainly use 2 floats to record coordinates.
ASKER
Thanks for your help on this but I'm far from clear on what I'm meant to do. Please can you explain the steps, assuming I'm an idiot?
Thanks
Thanks
Either
1) add extensions of postgis to postgresql server (that lets you query distances form SQL)
2) define your own type of pair of floats
3) just add 2 float columns to store coordinates
1) add extensions of postgis to postgresql server (that lets you query distances form SQL)
2) define your own type of pair of floats
3) just add 2 float columns to store coordinates
ASKER
ok thanks - I am going to be feeding these coords into geomapping so it sounds like the float type will work. Will try that and let you know
ASKER
ummm - float doesn't exist as a data type in the drop down list in pgadmin
however Latitude and longitude are appearing as data types - so that would be the answer then!
however Latitude and longitude are appearing as data types - so that would be the answer then!
It is real or double precision.
real is accurate to 6 digits
double otherwise (like your example)
real is accurate to 6 digits
double otherwise (like your example)
ASKER
The types longitude and latitude appear automatically in the drop down list if postgis is installed
http://www.sqlmanager.net/en/products/postgresql/dataimport/download