TransWikia.com

What type is this PostGIS data and how can I get lat, long from it?

Geographic Information Systems Asked by somevasya on May 18, 2021

I have an R dataframe, with location stored in a column. Location values look like this:

0101000020E6100000797A00E6B5593E40D5CE30B5A5394940

I was told that it is PostGIS data.

The goal is to visualize all those locations in R. My plan is to use ggmap, and for that I need to have lat, long values.

I have spun up a PSQL DB with PostGIS extension to try and poke at this data, but I have no clue what type it is, and treating it as varchar seems like a dead end. I have looked into PostGIS documentation on data types, but haven’t been able to find anything that resembles my data. My initial guess was that my data is of point type, but it doesn’t seem to be correct, since points are represented as pairs of numbers (for 2d), not as 51-chars-long strings.

Now I am at a loss. What type is this data and how can I get lat, long from it?

One Answer

If your point is located here:

enter image description here it's encoded as WKB which stands for Well Known Binary.

The exact same point, expressed as WKT (for Well Know Text, which is more human readable) is: 'POINT (30.3504317999999991 50.4503695000000008)'

Please see https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry
and https://portal.ogc.org/files/?artifact_id=25355 (page LXV).

These links may help you:

Working with PostGIS data in R?

Reading OGC geometry data from SQL server using R

https://journal.r-project.org/archive/2018/RJ-2018-025/RJ-2018-025.pdf

https://www.r-spatial.org/r/2016/09/01/bm.html

https://r-spatial.github.io/sf/articles/sf2.html#conversion-to-and-from-well-known-binary

Correct answer by s.k on May 18, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP