spatialite.sql

# http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html
# http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html

# $em->getConnection()->getNativeConnection()->loadExtension('mod_spatialite.dylib');

CREATE TABLE IF NOT EXISTS city (
    id integer primary key AUTOINCREMENT unique not null,
    name text not null
);

SELECT InitSpatialMetaData();
select AddGeometryColumn('city', 'loc', 4326, 'POINT', 'XY');
select CreateSpatialIndex('city', 'loc');

INSERT INTO
    city (id, name, loc)
VALUES
    (
        1,
        'Louny',
        MakePoint(50.354397837796604, 13.803626590794615, 4326)
    ),
    (
        2,
        'Kraków',
        MakePoint(50.03409465411076, 19.937277968461192, 4326)
    );

select
    name,
    x(loc) x,
    y(loc) y,
    round(
        distance(
            loc,
            MakePoint(50.0519528233593, 14.43861700065289, 4326),
            true
        ) / 1000
    ) as 'km from Prague'
from
    city;