Solved

Spatialite Native geometry support with SQLCreator

  • 24 November 2022
  • 2 replies
  • 7 views
ctredinnick
Rank
Userlevel 3
Badge +16

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.

icon

Best answer by danminneyatsaf 29 November 2022, 22:16

View original

2 replies

danminneyatsaf
Rank
Userlevel 2
Badge +10

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
Rank
Userlevel 3
Badge +16

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!

 

 

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
Members
Terms & ConditionsCookie settings
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.