Thread: Format of Pioint datatype.... lat/long or long/lat??
PostgreSQL has a geometric data type of "point". The format is listed as (x, y) but I am not sure if the X is to represent latitude or longitude. I have seen different systems that us X for either. Typically, coordinates should be read as lat/long but I have seen the opposite as well. An example in the help docs listed a point coordinate as x= longitude and y=latitude. Can someone clarify what the postgres standard is? What this will come down to is how built-in geospatial functions will interpret the point value. Thanks - Peter -- View this message in context: http://postgresql.1045698.n5.nabble.com/Format-of-Pioint-datatype-lat-long-or-long-lat-tp5784939.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/31/2013 12:45 PM, peterlen wrote: > PostgreSQL has a geometric data type of "point". The format is listed as (x, > y) but I am not sure if the X is to represent latitude or longitude. I have > seen different systems that us X for either. Typically, coordinates should > be read as lat/long but I have seen the opposite as well. An example in the > help docs listed a point coordinate as x= longitude and y=latitude. Can > someone clarify what the postgres standard is? What this will come down to > is how built-in geospatial functions will interpret the point value. Well the point data type is just that a point with x, y coordinates. The concept of longitude and latitude are higher order defined other systems that use the point type as a building block, i.e. Postgis. So which help docs did you see the x= longitude and y=latitude statement? > > Thanks - Peter > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Format-of-Pioint-datatype-lat-long-or-long-lat-tp5784939.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
On 12/31/2013 12:45 PM, peterlen wrote: > PostgreSQL has a geometric data type of "point". The format is listed as (x, > y) but I am not sure if the X is to represent latitude or longitude. I have > seen different systems that us X for either. Typically, coordinates should > be read as lat/long but I have seen the opposite as well. An example in the > help docs listed a point coordinate as x= longitude and y=latitude. Can > someone clarify what the postgres standard is? What this will come down to > is how built-in geospatial functions will interpret the point value. point is just x,y, it doesn't understand the spherical lat/long math (unless you install PostGIS and use its Geometry types which are fully aware of spherical coords), that said, Latitude is generally used as X (left/right, aka east/west), while Longitude is Y (up/down aka north/south) -- john r pierce 37N 122W somewhere on the middle of the left coast
Adrian - Thanks for the reply. The example was from http://www.postgresql.org/docs/9.1/static/tutorial-populate.html with the example of: INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); That is not a valid coordinate but it is clear that they are trying to declare it as longitude (-194) for x and latitude (53) for y. Yes, I would understand that it is up to other GIS clients to interpret those values as coordinates but they would need to know which value is which (lat or long). In the case above it would be easy to identify any value over 90 as being a longitude value but what if the values were listed as 10,40. That would represent two completely different points on the map if it were interpreted as lat/long compared to long/lat. This is why I was asking the question. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Format-of-Pioint-datatype-lat-long-or-long-lat-tp5784939p5784953.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
John - Appreciate the response. The reason why I asked this question is specifically for operations within PostGIS that will utilize the point values and so it is pretty important that the point values are entered correctly. Your description of X representing east/west and Y representing north/south is exactly why this is such a confusing issue because an east/west coordinate value is a longitude value while north/south is a latitude value so X can't represent an longitude value representing east/west. In the example I cited, it listed the X value as -194. While that value is invalid it is supposed to represent the longitude value. In one sense, I can see X representing latitude if you see X as the horizontal plane and Y as the vertical plane because latitude rings are horizontal and longitude rings are vertical, and again, this is why this can get confusing because people will interpret things differently. Part of this confusion with X vs Y stems from some of the JavaScript mapping APIs (like Google Maps and OpenLayers) where one API will list X as a latitude and the other will list X as a longitude. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Format-of-Pioint-datatype-lat-long-or-long-lat-tp5784939p5784954.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/31/2013 01:09 PM, John R Pierce wrote: > On 12/31/2013 12:45 PM, peterlen wrote: >> PostgreSQL has a geometric data type of "point". The format is listed >> as (x, >> y) but I am not sure if the X is to represent latitude or longitude. >> I have >> seen different systems that us X for either. Typically, coordinates >> should >> be read as lat/long but I have seen the opposite as well. An example >> in the >> help docs listed a point coordinate as x= longitude and y=latitude. Can >> someone clarify what the postgres standard is? What this will come >> down to >> is how built-in geospatial functions will interpret the point value. > > > point is just x,y, it doesn't understand the spherical lat/long math > (unless you install PostGIS and use its Geometry types which are fully > aware of spherical coords), that said, Latitude is generally used as X > (left/right, aka east/west), while Longitude is Y (up/down aka north/south) I thought it was the other way around? > > > > > -- Adrian Klaver adrian.klaver@gmail.com
On 12/31/2013 02:16 PM, peterlen wrote: > Adrian - Thanks for the reply. The example was from > http://www.postgresql.org/docs/9.1/static/tutorial-populate.html with the > example of: > > INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); > > That is not a valid coordinate but it is clear that they are trying to > declare it as longitude (-194) for x and latitude (53) for y. Yes, I would > understand that it is up to other GIS clients to interpret those values as > coordinates but they would need to know which value is which (lat or long). > In the case above it would be easy to identify any value over 90 as being a > longitude value but what if the values were listed as 10,40. That would > represent two completely different points on the map if it were interpreted > as lat/long compared to long/lat. This is why I was asking the question. One of those things that is best verified for a particular situation. For Postgis see here: http://postgis.org/docs/ST_MakePoint.html 'Note x is longitude and y is latitude' > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Format-of-Pioint-datatype-lat-long-or-long-lat-tp5784939p5784953.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
Perfect. That answers it. Thanks for providing that link. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Format-of-Pioint-datatype-lat-long-or-long-lat-tp5784939p5784960.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 12/31/2013 2:34 PM, Adrian Klaver wrote: >> point is just x,y, it doesn't understand the spherical lat/long math >> (unless you install PostGIS and use its Geometry types which are fully >> aware of spherical coords), that said, Latitude is generally used as X >> (left/right, aka east/west), while Longitude is Y (up/down aka >> north/south) > > I thought it was the other way around? arrrgh, you're right. LONGITUDE is east/west, LATITUDE Is north/south. so, its X = long, Y = lat. doy! sittin here at 37N -- john r pierce 37N 122W somewhere on the middle of the left coast
On 12/31/2013 03:06 PM, John R Pierce wrote: > On 12/31/2013 2:34 PM, Adrian Klaver wrote: >>> point is just x,y, it doesn't understand the spherical lat/long math >>> (unless you install PostGIS and use its Geometry types which are fully >>> aware of spherical coords), that said, Latitude is generally used as X >>> (left/right, aka east/west), while Longitude is Y (up/down aka >>> north/south) >> >> I thought it was the other way around? > > > arrrgh, you're right. LONGITUDE is east/west, LATITUDE Is > north/south. so, its X = long, Y = lat. > > doy! Yea, it is one of those inverse things, longitudinal lines run in a N-S direction but are plotted in the E-W direction and vice versa for latitude. Me remembering usually involves a certain amount of hand waving while I work it out on an imaginary globe:) > > sittin here at 37N > > -- Adrian Klaver adrian.klaver@gmail.com
trying to select a row of json type returning the entire json record table users id, jsondata jsondata has {"username":"jdoe", ....} select json_to_row from users where jsondata->"username" = "jdoe"; fails on operator -> unknown using debian testing with postgresql backport ppa psql: 9.3.2 server 9.2.6 do I need a higher server version ? is there a 9.3 server available for debian ?
On 02/01/2014 19:19, john.tiger wrote: > trying to select a row of json type returning the entire json record > > table users id, jsondata > jsondata has {"username":"jdoe", ....} > > select json_to_row from users where jsondata->"username" = "jdoe"; > fails on operator -> unknown > > using debian testing with postgresql backport ppa > psql: 9.3.2 server 9.2.6 > > do I need a higher server version ? is there a 9.3 server available for > debian ? Dunno about the JSON question, but you can get current Debian versions of PostgreSQL from http://apt.postgresql.org. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
For information which is sure to be up to date, check out: http://www.postgresql.org/download/linux/debian/
They describe how to add the repo to your Debian install, allowing you to update to PostgreSQL 9.3+.The JSON data type was implemented in 9.3. 9.4 (still under development,) brings some HUGE enhancements to the storage mechanism behind HSTORE and JSON, which I'd hop on the moment it is released as stable.
On Thu, Jan 2, 2014 at 3:37 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 02/01/2014 19:19, john.tiger wrote:Dunno about the JSON question, but you can get current Debian versions
> trying to select a row of json type returning the entire json record
>
> table users id, jsondata
> jsondata has {"username":"jdoe", ....}
>
> select json_to_row from users where jsondata->"username" = "jdoe";
> fails on operator -> unknown
>
> using debian testing with postgresql backport ppa
> psql: 9.3.2 server 9.2.6
>
> do I need a higher server version ? is there a 9.3 server available for
> debian ?
of PostgreSQL from http://apt.postgresql.org.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I take that back, basic JSON support was introduced in PostgreSQL 9.2, and a back-port of its functionality is also available for 9.1 here: https://bitbucket.org/adunstan/json_91
On Thu, Jan 2, 2014 at 4:04 PM, Joshua D. Burns <joshuadburns@hotmail.com> wrote:
For information which is sure to be up to date, check out: http://www.postgresql.org/download/linux/debian/They describe how to add the repo to your Debian install, allowing you to update to PostgreSQL 9.3+.The JSON data type was implemented in 9.3. 9.4 (still under development,) brings some HUGE enhancements to the storage mechanism behind HSTORE and JSON, which I'd hop on the moment it is released as stable.On Thu, Jan 2, 2014 at 3:37 PM, Raymond O'Donnell <rod@iol.ie> wrote:On 02/01/2014 19:19, john.tiger wrote:Dunno about the JSON question, but you can get current Debian versions
> trying to select a row of json type returning the entire json record
>
> table users id, jsondata
> jsondata has {"username":"jdoe", ....}
>
> select json_to_row from users where jsondata->"username" = "jdoe";
> fails on operator -> unknown
>
> using debian testing with postgresql backport ppa
> psql: 9.3.2 server 9.2.6
>
> do I need a higher server version ? is there a 9.3 server available for
> debian ?
of PostgreSQL from http://apt.postgresql.org.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 01/02/2014 11:19 AM, john.tiger wrote: > trying to select a row of json type returning the entire json record > > table users id, jsondata > jsondata has {"username":"jdoe", ....} > > select json_to_row from users where jsondata->"username" = "jdoe"; > fails on operator -> unknown > > using debian testing with postgresql backport ppa > psql: 9.3.2 server 9.2.6 > > do I need a higher server version ? is there a 9.3 server available for > debian ? > Yes, you need 9.3+ to get the -> operator. FYI, a good way to determine this is to go to the section you want, in this case: http://www.postgresql.org/docs/9.2/interactive/functions-json.html Then look at the top of page. There will be links to other versions of the Postgres for which this documentation applies. By moving backward/forward you can see when features appear or are changed. > > -- Adrian Klaver adrian.klaver@gmail.com