Обсуждение: 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

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