ST_Intersect with GeoJSON

[Elham Peiravian]

Hi Everyone,

I am fairly new to SpatiaLite and am still learning my way around it. 

I have a 'roads' table with a geometry column, and a GeoJSON polygon as a 'boundary'. I want to find the total length of all the roads that fall within this polygon. I have put bits and pieces of a query together but I feel I am missing out on a lot. I am still not sure how to particularly point o the GeoJSON polygon within my query.

This is what I have so far:

SELECT SUM(ST_Length(geometry)) As Total_Length from roads, boundary where ST_Intersects(roads.geometry, boundary.geometry);

I would greatly appreciate it if anyone can point me to the right direction.

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

Hi Elham,

don't confuse ST_Intersects() and ST_Intersection(); you need to
call both them in your query.

1. ST_Intersects(g1, g2)
this function will determine if any intersection exists
between the two geometries, and will return 1 (true) or
0 (false)

2. ST_Intersection(g1, g2)
this instead will return a new Geometry representing
the intersection between g1 and g2; if g1 and g2 do
not intersect it will return NULL

and this is how you have to rearrange your query:

SELECT SUM(ST_Length(
ST_Intersection(r.geometry, b.geometry))) As Total_Length
FROM roads AS r, boundary AS b
WHERE ST_Intersects(r.geometry, b.geometry) = 1;

bye Sandro

[Elham Peiravian]

Hi Sando,

Thank you so much for the clear explanation. I used the query you shared with me and the result comes back as NULL. I looked into it and noticed the geometry column in both tables is as such: BLOB sz=44 UNKNOWN type

I looked into it and tried to fix it by using RecoverGeometryColumn() but it didn't work. Any ideas?
: 

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

On Tue, 24 Jan 2023 06:39:32 -0800 (PST), Elham Peiravian wrote:
> Hi Sando,
>
> Thank you so much for the clear explanation. I used the query you
> shared with me and the result comes back as NULL. I looked into it
> and
> noticed the geometry column in both tables is as such: BLOB sz=44
> UNKNOWN type
>

Elham,

all Spatial SQL functions supported by SpatiaLite can only accept
Geometries encoded according to the internal binary format
BLOB-Geometry,
otherwise the result will always be a NULL.
and "BLOB UNKNOWN type" message definitely confirms that in your tables
there are no correctly encoded Geometries.

> I looked into it and tried to fix it by using RecoverGeometryColumn()
> but it didn't work.
>

RecoverGeometryColumn() can't magically recover a valid geometry
starting from an unknown BLOB; it's role is to properly register
a Spatial Table starting from a generic unqualified table, but
it must already contain a column storing valid BLOB-Geometries.

I notice that in your previous post you mentioned GeoJSON geometries;
you cannot use them as such, you have to convert them into the
BLOB-Geometry format accepted by SpatiaLite.

GeomFromGeoJSON ( geoJsonGeom )

this is the comverting function you need to use

bye Sandro

[Elham Peiravian]

Hi Sandro,

Thank you again for your prompt response. I am going to ask my question in a better way because I think I didn't fully mention what I am trying to do.

I have an .sqlite table of a 'roads' network. And I have a GeoJSON object for a 'boundary'.

I need to write a query where any time a 'boundary' is drawn over the network on web, the total length of the road segments within that boundary is calculated. I also think I should be transforming my data from GEOMETRIC to GEOGRAPHY to get the length in meters or kilometers.

This is what I have so far:

SELECT SUM(ST_Length(
ST_Intersection(r.geometry, b.geometry)geography)AS geography) As Total_Length
FROM roads AS r, GeomFromGeoJSON(boundary) AS b

WHERE ST_Intersects(r.geometry, b.geometry) = 1;

Obviously this is wrong cause it's not working.

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

On Thu, 26 Jan 2023 12:54:34 -0800 (PST), Elham Peiravian wrote:
> Hi Sandro,
>
> Thank you again for your prompt response. I am going to ask my
> question in a better way because I think I didn't fully mention what
> I
> am trying to do.
>
> I have an .sqlite table of a 'roads' network. And I have a GeoJSON
> object for a 'boundary'.
>
> I need to write a query where any time a 'boundary' is drawn over the
> network on web, the total length of the road segments within that
> boundary is calculated. I also think I should be transforming my data
> from GEOMETRIC to GEOGRAPHY to get the length in meters or
> kilometers.
>
> This is what I have so far:
>
> SELECT SUM(ST_Length(
> ST_Intersection(r.geometry, b.geometry)geography)AS geography) As
> Total_Length
> FROM roads AS r, GeomFromGeoJSON(boundary) AS b
> WHERE ST_Intersects(r.geometry, b.geometry) = 1;
>
> Obviously this is wrong cause it's not working.
>

Hi Elham,

the problem is not in the SQL query itself but in the data
stored in your tables.

All SpatiaLite SQL Spatial functions can only accept

Geometries encoded according to the internal binary

BLOB-Geometry format, but your data probably aren't
in this specific format.

just a quick check:

SELECT IsGeometryBLOB(geom), ST_GeometryType(geom), SRID(geom)
FROM table;

for a genuine Spatial Table the above query will return:

IsGeometryBLOB() = 1
ST_GeometryType() = (POINT | LINESTRING | POLYGON)
SRID = positive integer (probably but not necessarily 4326)

if some value is NULL this surely means that your data
are bogus, and in such case the only solution is to
restart from scratch creating and populating your
tables in a more appropriate form.

for further assistance let me know your actual findings;
it would be usefull knowing the origin of your data and
the methodology you' followed to create and populate
your Spatial Tables.

bye Sandro

[Elham Peiravian]

Hi Sandro,

Please disregard my previous message in which I stated that the geometry type of my table is unknown. I was mistakenly using the wrong data.

I have the correct data now that are legit. And to be 100% sure I did the test you recommended and the output was as you mentioned.

My query will be getting implemented into a script for a website, and the GeoJSON will be an object. My confusion is whether I am referencing the GeoJSON object correctly or not.

[Elham Peiravian]

Also, I need my output in km. I don't know how to correctly transform my output into GREOGRAPHY from GEOMETRY. 

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

On Fri, 27 Jan 2023 05:16:23 -0800 (PST), Elham Peiravian wrote:
> Hi Sandro,
>

> Please disregard my previous message in which I stated that the
> geometry type of my table is unknown. I was mistakenly using the
> wrong
> data.
> I have the correct data now that are legit. And to be 100% sure I did
> the test you recommended and the output was as you mentioned.
>
> My query will be getting implemented into a script for a website, and
> the GeoJSON will be an object. My confusion is whether I am
> referencing the GeoJSON object correctly or not.
>

there are just few details requiring to be fixed in your SQL Query;
let's where and why

a. ST_Intersection(r.geometry, b.geometry)geography

forget GEOGRAPHY in SpatiaLite, it's unsupported; the qualifier
following the SQL function will simply cause a syntax error

b. FROM roads AS r, GeomFromGeoJSON(boundary) AS b

yet another syntax error; you can legitimately create on the fly
a fake table just containing a single value, but not in this way


we are now ready to rewrite he query using the correct syntax:

SELECT Sum(ST_Length(
ST_Intersection(r.geometry, b.geometry) AS TotalLength
FROM roads AS r,
(SELECT GeomFromGeoJson(*BOUNDARY*) AS geometry) AS b
WHERE ST_Intersects(r.geometry, b.geometry = 1;

in which *BOUNDARY* is assumed to be a TEXT constant
containing some valid GeoJSON expression of the Polygon
type.
pay close attention: being a TEXT constant it must be
delimited by a couple of single quotes.

you can easily test for this using this reduced query:

SELECT IsGeometryBLOB(geom), ST_GeometryType(geom), SRID(geom)
FROM (SELECT GeomFromGeoJson(*BOUNDARY*) AS geom);

bye Sandro

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

On Fri, 27 Jan 2023 05:17:20 -0800 (PST), Elham Peiravian wrote:
> Also, I need my output in km. I don't know how to correctly transform
> my output into GREOGRAPHY from GEOMETRY. 
>

Elham,

SpatiaLite has no GEOGRAPHY, however when using lat/long coords
you can freely choose between lengths measured in degrees or in
meters.
for doing this you simply have to pass a further argument to
ST_Length()

the complete form of this function is:

ST_Length(geom, use_ellipsoid)

if the second optional argument is set to 1 (TRUE), then the
length will be calculated using sophisticated geodesic algorithms
and the returned result will be expressed in metres.

just for a final recapitulation:

- ST_Length(geom)
the result will be in degrees

- ST_Length(geom, 0)
the result will still be in dregrees

- ST_Length(geom, 1)
now the result will be in metres

bye Sandro

[Elham Peiravian]

I don't know how to reference the GeoJSON with a text constant?? Also, what would be the difference between ImportGeoJson() and GeomFromGeoJson? I need a table from the GeoJson with a geometry column to use for the intersection.

I'm trying this but the output is NULL:

SELECT ImportGeoJSON('Downloads/Richmondhill.geojson', 'boundary', 'geom');

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

On Fri, 27 Jan 2023 08:10:48 -0800 (PST), Elham Peiravian wrote:
> I don't know how to reference the GeoJSON with a text constant??
> Also,
> what would be the difference between ImportGeoJson() and
> GeomFromGeoJson? I need a table from the GeoJson with a geometry
> column to use for the intersection.
> I'm trying this but the output is NULL:
>
> SELECT ImportGeoJSON('Downloads/Richmondhill.geojson', 'boundary',
> 'geom');
>

- GeomFromGeoJSON() expects to receive a Text constant representing
aome valid GeoJSON expression, and on success will directly return
a binary GEOMETRY

- ImportFromGeoJSON() does a much more complex work:
1. it will check if the intended file exists on the file system
and it it can be read accordingly to the user access permissions
2. then the whole file is parsed and a corresponding table will
be created and populated within the DB
3. note that this function, for securoty reasons, requires to
set the externale variable SPATIALITE_SECURITY=1
4. short conclusion: it's a typical utility function intended
for interactive desktop environments, and I strongly doubt
that it will fit well in the highly dynamic context of
some web application.

the most usual way for dynamically passing GeoJSON expressions
from a web client to a web server is by using a little bit of
JavaScript, but rather obviusly this has nothing do to with
SpatiaLite itself.
I'm sorry but this discussion starts to be off topic.

bye Sandro