Skip to main content
Solved

Spatialite Native geometry support with SQLCreator

  • November 24, 2022
  • 2 replies
  • 123 views
ctredinnick
Supporter
Forum|alt.badge.img+19

I have been investigating replacing my use of SQLite Spatial (GDAL) with the new (FME 2022) SpatiaLite Native format.

Previously I used to be able to start a workspace with a SQL Creator with a statement like:

SELECT id, column, geometry

FROM table

 

With a script like that, the old SQLite Spatial (GDAL) format SQLCreator would read the geometry correctly from a database created by a SQLite Spatial writer.

However, I can't get the same to work with a SpatiaLite Native SQLCreator selecting from a database created by a SpatiaLite Native writer. Instead of reading the geometry of each feature, it reads a blank string attribute called geometry.

Is selecting a geometry with a SQL statement no longer supported? I can probably replace this sort of use with Readers, but would prefer to not.

Thanks.

Best answer by danminneyatsaf

Hi @ctredinnick​ first you'll need to make sure you know what the geometry column name is. By default the SpatiaLite Writer will create the spatial column with the name "geom". You can adjust this in the SpatiaLite Writer Feature Type with the Spatial Column parameter if you'd like.

 

In your SQLCreator, you can use the following query to read in the geom column as text:

SELECT Id, Column, AsText(geom)
FROM table

 

The result may look like the following for example:

image 

You can also be more specific with the query. For example if you want to query the first point in a feature, you can specify the column as AsText(StartPoint(geom)). More documentation on these different queries can be found here: https://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

2 replies

danminneyatsaf
Safer
Forum|alt.badge.img+13
  • danminneyatsafSafer
  • Safer
  • Best Answer
  • November 29, 2022

Hi @ctredinnick​ first you'll need to make sure you know what the geometry column name is. By default the SpatiaLite Writer will create the spatial column with the name "geom". You can adjust this in the SpatiaLite Writer Feature Type with the Spatial Column parameter if you'd like.

 

In your SQLCreator, you can use the following query to read in the geom column as text:

SELECT Id, Column, AsText(geom)
FROM table

 

The result may look like the following for example:

image 

You can also be more specific with the query. For example if you want to query the first point in a feature, you can specify the column as AsText(StartPoint(geom)). More documentation on these different queries can be found here: https://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html

ctredinnick
Supporter
Forum|alt.badge.img+19
  • ctredinnickSupporter
  • Author
  • Supporter
  • November 30, 2022

Aha, very useful to know that Spatialite has its own spatial functions which you can call. AsText and GeomFromText look suspiciously similar to the microsoft SQL Spatial functions STAsText and STGeomFromText I'm familiar with, so I think that'll work perfectly. Thanks!

 

 

Community Stats

32,154
Posts
122,048
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.