PostgreSQL: Get the trackpoints from a table containing GPX data into PostGIS geometry.

GPX_to_GIS.sql

-- i_am_spatial.raw_gpx_data's gpx_xml column contains GPX data stored as XML.
-- below query lists the trackpoints from this table, along with the file_id they belong to.
--
-- PostGIS is necessary to have the geometry data type available. Otherwise, the outermost SELECT can be removed.

SELECT 
    file_id,SELECT 
    file_id,
    point_time, 
    lat,
    lon,
    st_setsrid(st_point(lon, lat), 4326)  AS geometry
FROM (
    SELECT
        file_id,
        CAST(XMLSERIALIZE(content pointtime[1] AS text) AS timestamp) AS point_time, 
        CAST(XMLSERIALIZE(content lat[1] AS text) AS real) AS lat,
        CAST(XMLSERIALIZE(content lon[1] AS text) AS real) AS lon 
    FROM (
        SELECT 
            file_id, 
            xpath('//n:time/text()'::TEXT, coords, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) pointtime, 
            xpath('//n:trkpt/@lat'::TEXT, coords, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) lat, 
            xpath('//n:trkpt/@lon'::TEXT, coords, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) lon,
            coords 
        FROM (
            SELECT file_id, coords[generate_series(1, array_length(trackpoints_unsplitted.coords, 1))]  FROM (
                SELECT xpath('//n:trkpt', rgd.gpx_xml, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) AS coords,
                    rgd.file_id
                    FROM i_am_spatial.raw_gpx_data rgd
            ) AS  trackpoints_unsplitted
        ) AS trackpoints_splitted
    ) AS tracpoints_data_extracted
) AS trackpoints_data_serialized;
    point_time, 
    lat,
    lon,
    st_setsrid(st_point(lon, lat), 4326)  AS geometry
FROM (
    SELECT
        file_id,
        CAST(XMLSERIALIZE(content pointtime[1] AS text) AS timestamp) AS point_time, 
        CAST(XMLSERIALIZE(content lat[1] AS text) AS real) AS lat,
        CAST(XMLSERIALIZE(content lon[1] AS text) AS real) AS lon 
    FROM (
        SELECT 
            file_id, 
            xpath('//n:time/text()'::TEXT, coords, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) pointtime, 
            xpath('//n:trkpt/@lat'::TEXT, coords, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) lat, 
            xpath('//n:trkpt/@lon'::TEXT, coords, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) lon,
            coords 
        FROM (
            SELECT file_id, coords[generate_series(1, array_length(trackpoints_unsplitted.coords, 1))]  FROM (
                SELECT xpath('//n:trkpt', rgd.gpx_xml, '{{n,http://www.topografix.com/GPX/1/0}}'::TEXT[]) AS coords,
                    rgd.file_id
                    FROM raw_gpx_data rgd
            ) AS  trackpoints_unsplitted
        ) AS trackpoints_splitted
    ) AS tracpoints_data_extracted
) AS trackpoints_data_serialized;

LICENSE.md

ISC License

Copyright (c) 2020, Balint Erdosi [email protected]

Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted, provided that the above copyright notice and this permission notice appear in all copies.

THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.