1

I am new to using POSTGIS and PostgreSQL.

I am trying to convert PostGIS coordinates into lat and long values. When I copy one of the values and query like

SELECT ST_AsText('0101000000A7E8482EFFC55B402063EE5A42064140');

The query runs without an error. But when I try the query for the entire table column like

SELECT ST_AsText(GPS) from schema.table;

I am getting an error saying 

" 0101000000A7E8482EFFC" <-- parse error at position 22 within geometry

The column data type is text. I am unsure why the ST_AsText is unable to read the whole text and stops at position 21.

Additional information:
PostgreSQL version : 10.7
pgAdmin : 4.5
PostGIS: 2.4.3

I am using Python SQLAlchemy to Insert the tables directly into PostgreSQL.

Improve this question
6
  • If you're trying to convert existing postgis geometry into lat and lon, could you try using the ST_X(GEOM), and ST_Y(geom) functions to see if those work instead? And, is the geometry valid on the point that is throwing the error? Commented Jul 18, 2019 at 16:10
  • Also I see you've tagged pgadmin4 - try using DBeaver - the new geometry viewer is super useful, as well as dbeaver generally being a much better interface for an analyst vs. a database administrator... Commented Jul 18, 2019 at 16:10
  • 2
    is the GPS column storing PostGIS' GEOMETRY bytea representation as TEXT?? That column really should be the appropriate GEOMETRY type. I guess the actual string literal of bytea can be cast implicitly when passed to ST_AsText, but not when passed as column reference... Commented Jul 18, 2019 at 16:15
  • 1
    @ThingumaBob running \df++ st_asText show an (undocumented) version that gets a text input, cast it to geometry and call the common `st_asText(geometry)`` function. This cast should be valid on txt/bytea Commented Jul 18, 2019 at 17:00
  • @JGH aha! nice find, so you are absolutely right, it's the whitespaces rather than casting limtations. never thought someone would find this signature to be worth adding ,) Commented Jul 18, 2019 at 17:41

1 Answer 1

Reset to default
2

The error message mentions " 0101000000A7E8482EFFC", with a trailing space, which makes the geometry invalid.

The dirty solution is to trim your data

SELECT ST_AsText(TRIM(GPS)) from schema.table; 
with src as (select ' 0101000000A7E8482EFFC55B402063EE5A42064140'::text txt) select st_asText(txt::geometry) from src; ERROR: parse error - invalid geometry HINT: " 0101000000A" <-- parse error at position 12 within geometry with src as (select ' 0101000000A7E8482EFFC55B402063EE5A42064140'::text txt) select st_asText(trim(txt)) from src; st_astext ------------------------- POINT(111.0937 34.0489) (1 row)

The clean solution, as suggested in the comments by @ThingumBob, is to have a real geometry column and to sanitize what you put in it.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.