3

Summary:

I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to be able to iterate over these points using SQL and to access paths as arrays (or something functionally equivalent).


Background:

I'm a novice database user who is working with an unsupported legacy application which is using PostgreSQL 8.1 as a back-end database. I would like to be able to manually run some sanity checks on the data in the database in pure SQL (or PL/pgSQL), i.e. I'd like to be able to avoid using an external programming language, which is what I'm currently doing.

Several tables in the database include columns containing geometric data. Here is a description of one such table:

psql -U "${appuser}" -d "${appdatabase}" -c '\d+ path_table'

            Table "public.path_table"
 Column |  Type   | Modifiers | Description 
--------+---------+-----------+-------------
 id     | integer | not null  | 
 path   | path    | not null  | 
Indexes:
    "path_table_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "path_table_id_check" CHECK (id > 0)
Has OIDs: no

I consulted the section on Geometric Functions and Operators from the PostgreSQL 8.1 Manual, but to no avail.

Since this might be relevant, I'll mention that I believe that PostGIS 1.3.6 is installed, i.e.:

$ psql -U "${appuser}" -d "${appdatabase}" -c 'SELECT PostGIS_full_version();'

                                  postgis_full_version                                  
----------------------------------------------------------------------------------------
 POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS
(1 row)

My understanding is that I can get a list of all installed functions by using the psql \df meta-command, i.e.

$ psql -U "${appuser}" -d "${appdatabase}" -c '\df'

My understanding is also that all of the available PostGIS commands should be prefixed by the string 'ST_' (denoting spatial type), so that the following should produce a list of all the available PostGIS commands:

$ psql -U "${appuser}" -d "${appdatabase}" -Alt -c '\df' | cut -d'|' -f2 | grep -i '^st_'

Inspecting this list didn't produce any likely candidates besides the ST_Dump function, which doesn't appear to apply to a path type:

SELECT st_dump(path_column) FROM table;

ERROR:  function st_dump(path) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Apparently the st_dump argument needs to be of type geometry, so I tried following the hint and casting the path to a geometry type:

SELECT st_dump(path_column::geometry) FROM table;

ERROR:  cannot cast type path to geometry

I also consulted the Geometry Accessors of the PostGIS 1.3.6 Manual but apparently I don't know enough to be able to cull a solution from the documentation.

  • Can you show us the full definition of your table, please? And to fully understand, you want the WKT (Well-known text) of the data, for verification purposes? – Kassandry Nov 6 '15 at 20:46
  • 1
    8.1? Seriously? That's prehistoric. Anyway, maybe PostGIS was installed in a different schema that isn't on the search_path? Try \df *.ST*? (Assuming that works in 8.1) – Craig Ringer Nov 7 '15 at 7:32
  • Also, path is a built-in PostgreSQL data type, at least in current versions. Not a PostGIS type. I think PostGIS provides functions to convert path to geometry and otherwise work with it; maybe a look at the manual for your version will be informative. – Craig Ringer Nov 7 '15 at 7:36
  • @Kassandry Thank you for your response. I added a table description per your request; please let me know if that's not what you were looking for. I'm not sure how to get the WKT – the st_astext function is producing an error. – igal Nov 7 '15 at 8:48
  • @CraigRinger Unfortunately I'm completely serious. Your suggested command works, but I'm not sure what the purpose is – are you suggesting that another version of PostGIS might be installed somewhere? I apologize if my wording seemed to suggest that the path datatype was not a native type; I only mentioned PostGIS because I thought it might be relevant to the situation. Thank you for your help. – igal Nov 7 '15 at 8:52

Your Answer

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

Browse other questions tagged or ask your own question.