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.

  • 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? – DPSSpatial Jul 18 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... – DPSSpatial Jul 18 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... – ThingumaBob Jul 18 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 – JGH Jul 18 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 ,) – ThingumaBob Jul 18 at 17:41
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.