More

Setting SRID in Spatialite table?

Setting SRID in Spatialite table?


How do you update the SRID in a SpatiaLite table?

I am using the QspatiaLite plugin or the SpatiaLite_Gui program to send this SQL to the table:

UPDATE myTable SET geom = SETSRID(geom,26910)

… but I get:

violates Geometry constraint [geom-type or SRID not allowed]


Every spatial table in a SpatiaLite database has a foreign key constraint so that the SRID has to match the value stored in a metadata table namedgeometry_columns. You can see the constraint with:

SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = 'ggu_mytable_geom';

You can see the actual SRID enforced for the table with:

SELECT * FROM geometry_columns WHERE f_table_name = 'mytable';

Note that although you have usedmyTablein the example above, SpatiaLite enforces lower-case table and geometry column names in the metadata tables. If these queries don't work, you may have to use theLower()function to get the strings to match.

You can also see this in the SpatiaLite-GUI interface by going to the table's geometry column in the database tree in the left pane, right-clicking the column name and choosing Show Spatial Metadata.

Updating the SRID is easy, but you must change the expected SRID in thegeometry_columnstable first. Do this with:

UPDATE geometry_columns SET srid = 26910 WHERE f_table_name = 'mytable';

Then you can run the operation you were trying to:

UPDATE mytable SET geom = SetSRID(geom, 22910);

Geographic Database Functions¶

The functions documented on this page allow users to access geographic database functions to be used in annotations, aggregations, or filters in Django.

Not all backends support all functions, so refer to the documentation of each function to see if your database backend supports the function you want to use. If you call a geographic function on a backend that doesn’t support it, you’ll get a NotImplementedError exception.

Measurement Relationships Operations Editors Output format Miscellaneous
Area BoundingCircle Difference ForceRHR AsGeoJSON IsValid
Distance Centroid Intersection MakeValid AsGML MemSize
Length Envelope SymDifference Reverse AsKML NumGeometries
Perimeter PointOnSurface Union Scale SnapToGrid Transform Translate AsSVG GeoHash NumPoints

Availability: MySQL, Oracle, PostGIS, SpatiaLite

Accepts a single geographic field or expression and returns the area of the field as an Area measure. On MySQL, a raw float value is returned, as it’s not possible to automatically determine the unit of the field.


AsGeoJSON ¶

Availability: PostGIS, SpatiaLite

Accepts a single geographic field or expression and returns a GeoJSON representation of the geometry. Note that the result is not a complete GeoJSON structure but only the geometry key content of a GeoJSON structure. See also GeoJSON Serializer.

Keyword Argument Description
bbox Set this to True if you want the bounding box to be included in the returned GeoJSON.
crs Set this to True if you want the coordinate reference system to be included in the returned GeoJSON.
precision It may be used to specify the number of significant digits for the coordinates in the GeoJSON representation – the default value is 8.

Setting SRID in Spatialite table? - Geographic Information Systems

SpatiaLite ActiveRecord Adapter ¶ ↑

The SpatiaLite ActiveRecord Adapter is an ActiveRecord connection adapter based on the standard sqlite3 adapter. It extends the standard adapter to provide support for spatial extensions using SpatiaLite, using the RGeo library to represent spatial data in Ruby. Like the standard sqlite3 adapter, this adapter requires the sqlite3-ruby gem.

What This Adapter Provides ¶ ↑

First, this adapter extends the migration syntax to support creating spatial columns and indexes. To create a spatial column, use the :geometry type, or any of the OGC spatial types such as :point or :line_string . To create a spatial index, set the :spatial option to true.

When this adapter is in use, spatial attributes in your ActiveRecord objects will have RGeo geometry values. You can set spatial attributes either to RGeo geometry objects, or to strings in WKT (well-known text) format, which the adapter will automatically convert to geometry objects.

Spatial objects in RGeo are tied to a factory that specifies the coordinate system as well as other behaviors of the object. You must therefore specify a factory for each spatial column (attribute) in your ActiveRecord class. You can either set an explicit factory for a specific column, or provide a factory generator that will yield the appropriate factory for the table's spatial columns based on their types. For the former, call the set_rgeo_factory_for_column class method on your ActiveRecord class. For the latter, set the rgeo_factory_generator class attribute. This generator should understand at least the :srid option, which will be provided based on the column's specified SRID. Note that the spatialite adapter does not currently support Z or M coordinates, as it's unclear to me whether SpatiaLite itself supports them. The set_rgeo_factory_for_column and rgeo_factory_generator methods are actually implemented and documented in the “rgeo-activerecord” gem.

Examples, given the spatial table defined above:

Now you can interact with the data using the RGeo types:

You can create simple queries based on objective equality in the same way you would on a scalar column:

The adapter also provides experimental support for more complex queries such as radius searches. However, these extensions require Arel 2.1 (which is scheduled for release with Rails 3.1). We do not have these documented yet, and the syntax is subject to change. For now, you should write more complex queries in SQL.

Installation And Configuration ¶ ↑

Installing The Adapter Gem ¶ ↑

This adapter has the following requirements:

Ruby 1.8.7 or later. Ruby 1.9.2 or later preferred.

SpatiaLite 3.0 or later. Version 2.3 may work but is not officially supported.

ActiveRecord 3.0.3 or later. Earlier versions will not work. Should be compatible with Rails versions through 3.2.x.

rgeo-activerecord gem 0.4.3 or later.

Note: if you are running Mac OS X, the OS-provided copy of sqlite3 may not support extensions. You should install an alternate copy of sqlite3 (for example, using MacPorts or Homebrew) reinstall the gem using:

Install this adapter as a gem:

See the README for the “rgeo” gem, a required dependency, for further installation information.

To use this adapter, add this gem, “activerecord-spatialite-adapter”, to your Gemfile, and then request the adapter name “spatialite” in your database connection configuration (which, for a Rails application, is in the config/database.yml file). The other database connection configuration parameters are the same as for the stock sqlite3 adapter, with the exception of one additional parameter, libspatialite , which should be set to the full path to the libspatialite shared library, if it is not installed in a standard place (such as /usr/local or /opt/local).

Generally, you can create a new Rails application using:

…and then change the adapter names to “ spatialite ” and add an appropriate libspatialite setting.

Next, the SpatiaLite adapter includes a special railtie that provides support for SpatiaLite databases in ActiveRecord's rake tasks. This railtie is required in order to run, e.g., rake test. To install this railtie, you should add this line to your config/application.rb:

Note that this railtie must load after the ActiveRecord railtie. That is, the above require command should appear after require 'rails/all' .

Dealing with SpatiaLite Definitions ¶ ↑

SpatiaLite adds many objects (meta-information tables, functions, triggers, etc.) to a Sqlite3 database. These objects are required to maintain the spatial elements of the database, but they can be a hassle when managing the database with Rails. Following are some tips and gotchas that you may encounter.

Make sure you include the correct libspatialite setting in your database.yml config file, especially for your production environments.

SpatiaLite databases need to be initialized by executing the SpatiaLite initialization script or by calling the InitSpatialMetaData() function. The rake db:create task will do this for you when it creates a database. Thus, when setting up a new application, you should make sure you call rake db:create or otherwise cause the SpatiaLite initialization to occur, before you attempt to run your first migration. Failure to do so will result in errors during the migration.

Dumping a SpatiaLite database as SQL will cause a bunch of internal tables and triggers to be included in your dump. These are the actual SpatiaLite implementation objects used to enforce spatial constraints and implement spatial indexes. Unfortunately, not only is this a bit unsightly, but not everything is dumped here: for example, for each spatial column, there should be a row in the geometry_columns table, and those will be missing in the SQL structure dump. As a result, loading from the SQL structure dump will not properly reproduce your database schema. Because of this, we highly recommend that you leave config.active_record.schema_format set to :ruby for now, so that schema dumps are done in the Ruby format.

Known bugs and limitations ¶ ↑

The spatialite adapter works in principle, but there are a few known holes in the functionality. Notably, things that require the alter_table mechanism may not function properly, because the current sqlite3 implementation doesn't properly preserve triggers. This means, among other things, removing columns in tables with spatial information can cause the remaining spatial columns to fail. However, most simple things work, including creating tables with geometric columns, adding geometric columns to existing tables, and creating and removing spatial R*tree indexes. Note that this adapter is not yet well tested.

Contributions are welcome. Fork the project on Github.

Support available on the rgeo-users google group at groups.google.com/group/rgeo-users

Contact the author at dazuma at gmail dot com.

The SpatiaLite Adapter and its supporting libraries (including RGeo) are written by Daniel Azuma (www.daniel-azuma.com).

Development is supported by Pirq. (www.pirq.com).

Copyright 2010-2012 Daniel Azuma

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

Neither the name of the copyright holder, nor the names of any other contributors to this software, may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES LOSS OF USE, DATA, OR PROFITS OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.


Making the Transform

(I have to admit, I was amazed when this next bit worked.)

Let’s say we have to identify all the VOR stations in Canada, and draw a 25 km exclusion buffer around them (hey, someone might want to …). VOR stations can be queried from TAFL using the following criteria:

  • the licensee is Nav Canada, or similar,
  • the TX frequency is between 108–117.96 MHz,
  • the location contains ‘VOR’.

which returns a list of 67 stations, from VYT80 on Mount Macintyre, YT to YYT St Johns, NL. We can use this, along with the UTM zone query above, to make beautiful, beautiful circles:

Yes, they look oval don’t forget that geographic coordinates don’t maintain rectilinearity. Transformed to UTM, they look much more circular:

Share this:


Setting SRID in Spatialite table? - Geographic Information Systems

The ST_SPATIAL_REFERENCE_SYSTEMS table provides information about available spatial reference systems (SRSs) for spatial data. This table is based on the SQL/MM (ISO/IEC 13249-3) standard.

Entries in the ST_SPATIAL_REFERENCE_SYSTEMS table are based on the European Petroleum Survey Group (EPSG) data set, except for SRID 0, which corresponds to a special SRS used in MySQL that represents an infinite flat Cartesian plane with no units assigned to its axes. For additional information about SRSs, see Section 11.4.5, “Spatial Reference System Support”.

The spatial reference system name. This value is unique.

The spatial reference system numeric ID. This value is unique.

SRS_ID values represent the same kind of values as the SRID of geometry values or passed as the SRID argument to spatial functions. SRID 0 (the unitless Cartesian plane) is special. It is always a legal spatial reference system ID and can be used in any computations on spatial data that depend on SRID values.

The name of the organization that defined the coordinate system on which the spatial reference system is based.

The numeric ID given to the spatial reference system by the organization that defined it.

The spatial reference system definition. DEFINITION values are WKT values, represented as specified in the Open Geospatial Consortium document OGC 12-063r5.

SRS definition parsing occurs on demand when definitions are needed by GIS functions. Parsed definitions are stored in the data dictionary cache to enable reuse and avoid incurring parsing overhead for every statement that needs SRS information.

The spatial reference system description.

Notes

The SRS_NAME , ORGANIZATION , ORGANIZATION_COORDSYS_ID , and DESCRIPTION columns contain information that may be of interest to users, but they are not used by MySQL.

Example

This entry describes the SRS used for GPS systems. It has a name ( SRS_NAME ) of WGS 84 and an ID ( SRS_ID ) of 4326, which is the ID used by the European Petroleum Survey Group (EPSG).

The DEFINITION values for projected and geographic SRSs begin with PROJCS and GEOGCS , respectively. The definition for SRID 0 is special and has an empty DEFINITION value. The following query determines how many entries in the ST_SPATIAL_REFERENCE_SYSTEMS table correspond to projected, geographic, and other SRSs, based on DEFINITION values:

To enable manipulation of SRS entries stored in the data dictionary, MySQL provides these SQL statements:

CREATE SPATIAL REFERENCE SYSTEM : See Section 13.1.19, “CREATE SPATIAL REFERENCE SYSTEM Statement”. The description for this statement includes additional information about SRS components.


Mdhntd

Did Milano or Benatar approve or comment on their namesake MCU ships?

Are there any important biographies of nobodies?

How do governments keep track of their issued currency?

Why VGA framebuffer was limited to 64kB window?

Medieval flying castle propulsion

Why was the Sega Genesis marketed as a 16-bit console?

How to handle self harm scars on the arm in work environment?

What is wrong with this proof that symmetric matrices commute?

Should I give professor gift at the beginning of my PhD?

Cycle through MeshStyle directives in ListLinePlot

Using "subway" as name for London Underground?

Should I avoid hard-packed crusher dust trails with my hybrid?

Existence of a pointwise convergent subsequence

What can I, as a user, do about offensive reviews in App Store?

SOQL Not Recognizing Field?

What to do when surprise and a high initiative roll conflict with the narrative?

Can U.S. Tax Forms Be Legally HTMLified?

Winning Strategy for the Magician and his Apprentice

What is the actual quality of machine translations?

Second (easy access) account in case my bank screws up

How can electric fields be used to detect cracks in metals?

Error: Geometry constraint geom-type or SRID not allowed. How to ensure the correct geom-type?

Insert Geopandas GeoDataFrame into Spatialite database

Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:

The code worked fine when I was using point data. However, when I use vector data I keep getting the error
your_table.geog violates Geometry constraint [geom-type or SRID not allowed]
I know the SRID is ok, so from the error I assume it is the geom-type that is incorrect. I have checked the geom-type in a couple ways: 1. opening the layer in QGIS and checking the porperties and 2. opening the database in spatialite-gui and 'check geometries'. I have also tried using polygon, multipolygon and multipolygonz as the 4th argument in the AddGeometryColumn selection, but I get the same error. So, what is the best way to check which geometry it should be?

That happens usually with shapefiles which contain a mixture of polygons and multipolygons. If you initialize the table for polygons the multipolygons are rejected and vice versa. You have two options 1) initialize the table as a generic "GEOMETRY" and it accepts any geometries, but some sofware will have problems with dealing such table or 2) initialize the table for multipolygons and insert geometries through the CastToMultiPolygon function gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

@user30184. Thank you for you answer. I used the second one and it worked

Please write as an answer what, and how did you do that.

Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:

The code worked fine when I was using point data. However, when I use vector data I keep getting the error
your_table.geog violates Geometry constraint [geom-type or SRID not allowed]
I know the SRID is ok, so from the error I assume it is the geom-type that is incorrect. I have checked the geom-type in a couple ways: 1. opening the layer in QGIS and checking the porperties and 2. opening the database in spatialite-gui and 'check geometries'. I have also tried using polygon, multipolygon and multipolygonz as the 4th argument in the AddGeometryColumn selection, but I get the same error. So, what is the best way to check which geometry it should be?

That happens usually with shapefiles which contain a mixture of polygons and multipolygons. If you initialize the table for polygons the multipolygons are rejected and vice versa. You have two options 1) initialize the table as a generic "GEOMETRY" and it accepts any geometries, but some sofware will have problems with dealing such table or 2) initialize the table for multipolygons and insert geometries through the CastToMultiPolygon function gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

@user30184. Thank you for you answer. I used the second one and it worked

Please write as an answer what, and how did you do that.

Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:

The code worked fine when I was using point data. However, when I use vector data I keep getting the error
your_table.geog violates Geometry constraint [geom-type or SRID not allowed]
I know the SRID is ok, so from the error I assume it is the geom-type that is incorrect. I have checked the geom-type in a couple ways: 1. opening the layer in QGIS and checking the porperties and 2. opening the database in spatialite-gui and 'check geometries'. I have also tried using polygon, multipolygon and multipolygonz as the 4th argument in the AddGeometryColumn selection, but I get the same error. So, what is the best way to check which geometry it should be?

Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:

The code worked fine when I was using point data. However, when I use vector data I keep getting the error
your_table.geog violates Geometry constraint [geom-type or SRID not allowed]
I know the SRID is ok, so from the error I assume it is the geom-type that is incorrect. I have checked the geom-type in a couple ways: 1. opening the layer in QGIS and checking the porperties and 2. opening the database in spatialite-gui and 'check geometries'. I have also tried using polygon, multipolygon and multipolygonz as the 4th argument in the AddGeometryColumn selection, but I get the same error. So, what is the best way to check which geometry it should be?

That happens usually with shapefiles which contain a mixture of polygons and multipolygons. If you initialize the table for polygons the multipolygons are rejected and vice versa. You have two options 1) initialize the table as a generic "GEOMETRY" and it accepts any geometries, but some sofware will have problems with dealing such table or 2) initialize the table for multipolygons and insert geometries through the CastToMultiPolygon function gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

@user30184. Thank you for you answer. I used the second one and it worked

Please write as an answer what, and how did you do that.

That happens usually with shapefiles which contain a mixture of polygons and multipolygons. If you initialize the table for polygons the multipolygons are rejected and vice versa. You have two options 1) initialize the table as a generic "GEOMETRY" and it accepts any geometries, but some sofware will have problems with dealing such table or 2) initialize the table for multipolygons and insert geometries through the CastToMultiPolygon function gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

@user30184. Thank you for you answer. I used the second one and it worked

Please write as an answer what, and how did you do that.

That happens usually with shapefiles which contain a mixture of polygons and multipolygons. If you initialize the table for polygons the multipolygons are rejected and vice versa. You have two options 1) initialize the table as a generic "GEOMETRY" and it accepts any geometries, but some sofware will have problems with dealing such table or 2) initialize the table for multipolygons and insert geometries through the CastToMultiPolygon function gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

That happens usually with shapefiles which contain a mixture of polygons and multipolygons. If you initialize the table for polygons the multipolygons are rejected and vice versa. You have two options 1) initialize the table as a generic "GEOMETRY" and it accepts any geometries, but some sofware will have problems with dealing such table or 2) initialize the table for multipolygons and insert geometries through the CastToMultiPolygon function gaia-gis.it/gaia-sins/spatialite-sql-latest.html.

@user30184. Thank you for you answer. I used the second one and it worked

@user30184. Thank you for you answer. I used the second one and it worked

Please write as an answer what, and how did you do that.

Please write as an answer what, and how did you do that.


We should be ready to save spatial data. Create a test which simply saves a climbing area and retrieves it.

And hey presto! You should be able to see the area being saved:

This is really cool. We can now leverage the power of SQL Server’s spatial processing (efficient distance calculations, spatial relationship analysis, spatial indexing etc) but largely shield this underneath the abstraction that NHibernate provides. The only painful bit now is how to allow the domain entities and services to actually use these functions (e.g. STIntersects): currently I am using stored procedures.


Spatial Lookups¶

GeoDjango's lookup types may be used with any manager method like filter() , exclude() , etc. However, the lookup types unique to GeoDjango are only available on geometry fields. Filters on 'normal' fields (e.g. CharField ) may be chained with those on geographic fields. Thus, geographic queries take the following general form (assuming the Zipcode model used in the GeoDjango Model API):

In this case, poly is the geographic field, contains is the spatial lookup type, and pnt is the parameter (which may be a GEOSGeometry object or a string of GeoJSON , WKT, or HEXEWKB).

A complete reference can be found in the spatial lookup reference.

GeoDjango constructs spatial SQL with the GeoQuerySet , a subclass of QuerySet . The GeoManager instance attached to your model is what enables use of GeoQuerySet .


Note: these pages are no longer maintained

what's this SRID stuff ? .
I've never heard this term before now .

What's this SRID stuff ? . I've never heard this term before now .

Planet Earth is a sphere . not exactly, planet Earth has an ellipsoidal shape ( slightly flattened at poles ) .
oh no, that's absolutely wrong: planet Earth hasn't a geometric regular shape, it actually is a geoid

All the above assertions can be assumed to be true, but at different approximation levels.
Near the Equator differences between a sphere and an ellipsoid are rather slight and quite unnoticeable but neat both Poles such differences becomes greater and most easily appreciable.
For many practical purposes differences between an ellipsoid and a geoid are very slim: but for long range aircraft navigation (or even worse, for satellite positioning), this is too much simplistic and unacceptably approximate.

Anyway, whatsoever could be the real shape of the Earth, position of each point on the planet surface can precisely determined simply measuring two angles : longitude and latitude .
In order to set a complete Spatial Reference System [aka SRS ] we can use the Poles and the Equator ( which after all are outstanding places by intrinsic astronomic properties ): choosing a Prime Meridian on the other side is absolutely conventional: but since many centuries ( Britannia rule the waves . ) adopting the Greenwich Meridian is an obvious choice.

  • paper sheets (and monitor screens) are absolutely flat they don't look at all like a sphere
  • using angles makes measuring distances and areas really difficult and counter-intuitive.

Apparently this map projection introduces severe and not acceptable deformations: but when you carefully focus your attention on the narrow central fuse, you'll immediately recognize that UTM allows to get a nearly perfect planar projection of excellent quality.
Anyway all this has a price: the central fuse has to be really narrow (let say, it will span only few degrees on both sides). As the fuse becomes wider, as much more deformations will become stronger and more evident.

Accordingly to all the above considerations, UTM defines 60 standard zones, each one covering exactly 6 longitude degrees.
Merging together two adjacent fuses (12 degrees) obviously reduces accuracy, but is still acceptable for many practical purposes: exceeding this limit produces really low-quality results, and has to be absolutely avoided.

Attempting to standardize the chaos


During the past two centuries every National State has introduced at least one (and very often, more than one) map projection system and related SRS: the overall result is absolutely chaotic (and really painful to be handled).

Happily, an international standard is widely adopted so to make easier correctly handling map SRS: the European Petroleum Survey Group [ EPSG ] maintains a huge worldwide dataset of more than 3,700 different entries.
Many of them are nowadays obsolete, and simply play a historical role many others are only useful in very limited national boundaries.
Anyway, this one is an absolutely impressive collection.
And each single entry within the EPSG dataset is uniquely identified by its numeric ID and descriptive name , so to avoid any possible confusion and ambiguity.

Any Spatial DBMS requires some SRID-value to be specified for each Geometry: but such SRID simply is a Spatial Reference ID , and ( hopefully ) coincides with the corresponding EPSG ID

Just in order to help you understand better this SRID chaos, this is a quite complete list of SRIDs often used in a ( small ) Nation such as Italy :

EPSG SRID Name Notes
4326 WGS 84 Geographic [ long-lat ] worldwide used by GPS devices
3003
3004
Monte Mario / Italy zone 1
Monte Mario / Italy zone 2
obsolete (1940) but still commonly used
23032
23033
ED50 / UTM zone 32N
ED50 / UTM zone 33N
superseded and rarely used: European Datum 1950
32632
32633
WGS 84 / UTM zone 32N
WGS 84 / UTM zone 33N
WGS84, adopting the planar UTM projection
25832
25833
ETRS89 / UTM zone 32N
ETRS89 / UTM zone 33N
enhanced evolution of WGS84: official EU standard

And the following examples may help to understand even better:

Town SRID Coordinates
X (longitude) Y (latitude)
Roma 4326 12.483900 41.894740
3003 1789036.071860 4644043.280244
23032 789036.071860 4644043.280244
32632 789022.867800 4643960.982152
25832 789022.867802 4643960.982036
Milano 4326 9.189510 45.464270
3003 1514815.861095 5034638.873050
23032 514815.861095 5034638.873050
32632 514815.171223 5034544.482565
25832 514815.171223 5034544.482445

  • WGS84 [4326] coordinates are expressed in decimal degrees , because this one is a Geographic System directly based on long-lat angles.
  • on the other side any other system adopts coordinates expressed in meters : all them are projected aka planar systems.
  • Y-values look very similar for every planar SRS: that's not surprising, because this value simply represents the distance from the Equator.
  • X-values are more dispersed, because different SRSes adopt different false easting origins: i.e. they place their Prime Meridian in different ( conventional ) places.
  • Anyway, any UTM-based SRS gives very closely related values, simply because all them share the same UTM zone 32 definition.
  • The ( small ) differences you can notice about different UTM-based SRSes can be easily explained: UTM zone 32 is always the same, but the underlying ellipsoid changes each time.
    Getting a precise measure for ellipsoid's axes isn't an easy task: and obviously during the time several increasingly better and most accurate estimates has been progressively adopted.
  • Using WGS84 [4326] geographic, long-lat coordinates we'll actually get a measure corresponding to an angle expressed in decimal degrees . [ not so useful, really . ]
  • any other SRS will return a distance measure expressed in meters : anyway, as you can easily notice, figures aren't exactly the same.
  • Great Circle distances are calculated assuming that the Earth is exactly a sphere: and this one obviously is the worst estimate we can get.
  • on the other side Geodesic distances are directly calculated on the reference Ellipsoid.

But this isn't at all surprising in physical and natural sciences: any measured value is intrinsically affected by errors and approximations.
And any calculated value will be inexorably affected by rounding and truncation artifacts.


Watch the video: SQLite 03: Importing EXCEL into SQLite