return XY coordiantes from Linestring

[Tara]

Hi,

I have a roads table in spatialite, now I want to extract the XY coordinates of the BLOB Geometry, I suppose that each row is representing a linestring , I want to save it as an array, in order to display the lines on my leaflet map. Is there a way to extract XY coordinates of the lines , like ST_X()  or ST_Y ??

I am trying to get a result like this:

var latlngs = [
    [[45.51, -122.68],
    [37.77, -122.43],
    [34.04, -118.2]], 

    [[45.51, -122.68],
    [37.77, -122.43],
    [34.04, -118.2]], 
];

[Jukka Rahkonen]

Hi,

Have you considered to use AsGeoJSON
https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html?

Usage example with inline geometry:

select AsGeoJSON(ST_GeomFromText('LINESTRING (0 0, 1 1)'));

Result:
{"type":"LineString","coordinates":[[0,0],[1,1]]}

-Jukka Rahkonen-

> --
> You received this message because you are subscribed to the Google
> Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to
> spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.

[mj10777]

The nearest you will find is: AsGeoJSON(geometry):

- which for a Linestring looks like this:

{"type":"LineString","coordinates":[[3622819.316282636,3254593.150963568],[3641599.753619762,3257954.422418792]]}

 After removing everything upto '[[' and removing the tailing '}', you could adapt it to your needs.

With some basic sqlite command, you can parse the result:

https://sqlite.org/lang_corefunc.html

A quick and dirty sample:

SELECT

 'var latlngs = ['||replace(substr(AsGeoJSON(eur_linestring),instr(AsGeoJSON(eur_linestring),'[[')),'}','')||',];' AS var_latlong

FROM "middle_earth_bridges"

LIMIT 1;

would return this:

var latlngs = [[[3622819.316282636,3254593.150963568],[3641599.753619762,3257954.422418792]],];

Hope this helps,

Mark

[a.fu...@lqt.it]

Hi Tara,

directly extracting all (X,Y) coordinates out from a possibly
complex binary Geometry object is not a problem you can easily
resolve just using a pure SQL approach.

as previously suggested by Jukka and Mark, you could eventually
attempt to grammatically parse the text strings returned by
AsGeoJSON(); it will be rather simple to be implemented for
the simpler LINESTRING and MULTIPOINT cases, but it will quickly
become too much complex for Geometries of the POLYGON,
MULTILINESTRING, MULTIPOLYGON and GEOMETRYCOLLECTION types.

an alternative "pure SQL" approach could be based on SQL
functions such as ST_NumGeometries(), ST_GeometryN(),
ST_ExterionRing(), ST_NumInteriorRings(), ST_InterionRingN(),
ST_NumPoints(), ST_PointN() and ST_X(), ST_Y().
this second approach will easily trigger an huge number of SQL
queries when processing real world Geometries (as e.g. complex
Polygons defined by an outer ring and several inner rings, for
a total number of points easily exceeding several thousands),
and could easily be highly inefficient and terribly slow.

a critical point not to be overlooked: both AsGeoJSON() and
ST_X(), ST_Y() will silently translate the internal Double
Precision coordinates into text strings presenting a limited
precision, thus causing unexpected rounding/truncation effects.

if I'm not wrong you are using SpatiaLite from within a
Python program; in this case the best tool for the trade is
using pyGDAL [1], a powerful module allowing to directly
manipulate binary Geometries [2],[3],[4] and [5].

[1] https://pcjericks.github.io/py-gdalogr-cookbook/
[2]
https://pcjericks.github.io/py-gdalogr-cookbook/geometry.html#count-points-in-a-geometry
[3]
https://pcjericks.github.io/py-gdalogr-cookbook/geometry.html#count-geometries-in-a-geometry
[4]
https://pcjericks.github.io/py-gdalogr-cookbook/geometry.html#iterate-over-geometries-in-a-geometry
[5]
https://pcjericks.github.io/py-gdalogr-cookbook/geometry.html#iterate-over-points-in-a-geometry

bye Sandro

[Tara]

Thanks for your hints. I could load the Geojson data, however I couldn't fix it to extract the coordinates for my leaflet web page. My code looks like this:

$.get(my_url, function( data ) {
        obj = JSON.parse(data);
        for (i=0; i< obj.length; i++) {
            var roads = obj [i][3];  
            console.log(roads);
        }       
        });

The result is:

{"type":"MultiLineString","coordinates":[[[9.231246999999999,48.482832],[9.231028999999999,48.48272699999999],[9.230776,48.48265899999999],[9.230518999999999,48.482582],[9.230449,48.48249199999999],[9.230527999999999,48.482405]]]}

...

...

Is there a way to extract the coordiantes in javascript?

[br...@frogmouth.net]

I’m not sure I fully understand the problem, but you should be able to just de-reference the object.

 

So if you want the first latitude (48.482832), just do:

roads.coordinates[0][0][1]

 

Brad

--

[Jukka Rahkonen]

Hi,

Because Leaflet has a notive support for GeoJSON
http://leafletjs.com/examples/geojson/ why don't you use that? Anyway I
think that questions about clielt side javascript handling of
JSON/GeoJSON are off-topic on this forum. I would consider asking from
gis.stackexchange instead.

-Jukka Rahkonen-