using Spacialite as simple geolocation index

[Nesvarbu Nereikia]

Hi,

I need a simple/fast way to geographically index latitude/longitude points.

My use-cases:

1. Import lat/lon pairs covering Europe into index.

2. Get all points belonging to e.g DE/Berlin or DE/Berlin/Mitte

Secondary use-case:

 Get all cities of a country e.g. DE

 Get all districts of a city e.g. DE/Berlin with their central coordinates and number of points they have in them.

Creation of index (import of points) should be fast.

Is Spatialite a good tool for this?

Is there a "scalfolding" "base" DB with European locations already available somewhere?

Please direct me to a tutorial or something for this use case.

Thanks a bunch for this tool!

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

On Fri, 19 Aug 2022 09:28:22 -0700 (PDT), Nesvarbu Nereikia wrote:
> Hi,
>
> I need a simple/fast way to geographically index latitude/longitude
> points.
> My use-cases:
> 1. Import lat/lon pairs covering Europe into index.
> 2. Get all points belonging to e.g DE/Berlin or DE/Berlin/Mitte
> Secondary use-case:
> Get all cities of a country e.g. DE
> Get all districts of a city e.g. DE/Berlin with their central
> coordinates and number of points they have in them.
>
> Creation of index (import of points) should be fast.
> Is Spatialite a good tool for this?
>

Hi,

SpatiaLite is certainly able to resolve this class of problems,
but being a full fledged Spatial DMBS it requires a rather
sound knowledge of SQL and Spatial SQL.

Nothing impossible or too difficult, but for sure it will
take at least a full week of careful study before you can
get some useful result.

I'm not sure that this prerequisite matches your expectations
for "simple/fast".

May be I'm wrong, but I have the impression that your are
looking for something simpler than SpatiaLite.

Just a clarification: to be able to identify all the cities
of Germany a spatial index alone is not enough.
You must also have a layer of polygons representing the
country's borders.
And you need a second layer of polygons representing municipal
boundaries to get the list of all points within Berlin.
A third layer of polygons representing district boundaries is
required for selecting all points within Berlin/Mitte
... and so on ...

> Is there a "scalfolding" "base" DB with European locations already
> available somewhere?
>

check out GeoNames:

https://www.geonames.org/
http://download.geonames.org/export/dump/

bye Sandro

[Nesvarbu Nereikia]

Thank you Sandro, got it.

What about using spatialite for reverse geocoding? Basically you give it a coordinate - it returns location info (country/city/district etc...)

Is there a DB I can clone/ or prepare for such a service?

There is a nominatim service self host option. But it is PostgreSQL based and "heavy" to host.

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

On Fri, 19 Aug 2022 11:27:59 -0700 (PDT), Nesvarbu Nereikia wrote:
> Thank you Sandro, got it.
> What about using spatialite for reverse geocoding? Basically you give
> it a coordinate - it returns location info (country/city/district
> etc...)
> Is there a DB I can clone/ or prepare for such a service?
> There is a nominatim service self host option. But it is PostgreSQL
> based and "heavy" to host.
>

Hi,

both SQLite/Spatialite and Postgres/PostGIS are Spatial DBMS
and roughly speacking they are in the same class.

1) first you need to get all the data you need (map layers,
admin boundaries and so on).
using the one or the other makes no difference, the
difficulties are exactly the same.

2) then you have to design an appropriate data model
in terms of tables, primary keys / foreign keys
relationships, related indices and spatial indices.
once again using the one or the other makes a very
little difference; PostgreSQL is a little bit
harder because you have to handle users and their
permissions.

3) at this point you'll be ready for populating your
DB: this usually implies importing data from
Shapefiles, CSV files and alike.
all these activities are ususlly simpler on
SpatiaLite, most notably if you learn to
use the GUI tool.

4) it's now time to write, test and optimize
all the SQL queryies required by your app.
once again using the one or the other makes
a very little difference, difficulties are
more or less the same on both DBMS.
taking full profit from the Spatial Index
is surely harder on SpatiaLite.

5) last final step: deploying the DBMS and the
app on a remote web server.
here SpatiaLite wins hands down for its
simplicity and easyness.
but it's only the final step of a longer
process.

conclusion: maybe I'm wrong, but it seems to me
that you are underestimating the real problems:

A. get all the basic data you need; it might
be a lot harder than you think.

B. inventing a good algorithm for reverse geocoding
and translate it into SQL terms; not necessarily
a simple task.

choosing between PostgreSQL and SpatiaLite is not
the most pressing of your priorities: the real problem
seems to be finding solutions for A. and B. (and
SpatiaLite has nothing to do with them).

bue Sandro

[Peter Johnson]

You can find all the geographic data you need for Germany in this SpatiaLite database:

https://data.geocode.earth/wof/dist/spatial/whosonfirst-data-admin-de-latest.spatial.db.bz2

All data contained is open, so either licensed CC-0/ CC-BY or equivalent.

For other countries visit:

https://geocode.earth/data/whosonfirst/

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/9c1378bf-c9bb-428b-bb03-b8346cfb0969n%40googlegroups.com.