spatialite_example.sql

.load mod_spatialite

-- Inicializando tablas de metadata
BEGIN;
SELECT initSpatialMetadata();
COMMIT;

-- Observar tablas creadas
.tables

-- Tabla de poligonos
CREATE TABLE cerco (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, nombre TEXT NOT NULL);
SELECT addGeometryColumn('cerco', 'area', 4326, 'POLYGON', 2);

INSERT INTO cerco (nombre, area) VALUES ('Yanahuara', st_geomFromText('POLYGON((-71.5452003479004 -16.3805913530897,-71.5515518188477 -16.4014239754096,-71.5437412261963 -16.4057055031012,-71.5346431732178 -16.3815795119829,-71.5452003479004 -16.3805913530897))', 4326));
INSERT INTO cerco (nombre, area) VALUES ('Cercado', st_geomFromText('POLYGON((-71.5340423583984 -16.3796855363701,-71.5442562103271 -16.4073522194401,-71.5380764007568 -16.410563276217,-71.5188503265381 -16.4052938218392,-71.5185928344727 -16.3815795119829,-71.5340423583984 -16.3796855363701))', 4326));
INSERT INTO cerco (nombre, area) VALUES ('Cayma', st_geomFromText('POLYGON((-71.5476036071777 -16.3867672639725,-71.5533542633057 -16.3807560465864,-71.5523242950439 -16.3272233406407,-71.5331840515137 -16.3287059823086,-71.5436553955078 -16.3803443125836,-71.5458011627197 -16.3800972717645,-71.5476036071777 -16.3867672639725))', 4326));

-- Tabla de lugares
CREATE TABLE lugar (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, nombre TEXT NOT NULL);
SELECT addGeometryColumn('lugar', 'ubicacion', 4326, 'POINT', 2);

INSERT INTO lugar (nombre, ubicacion) VALUES('Fuera', st_geomFromText('POINT(-71.5285491943359 -16.3652742498081)', 4326));
INSERT INTO lugar (nombre, ubicacion) VALUES('En Cayma 1', st_geomFromText('POINT(-71.5446853637695 -16.358521166359)', 4326));
INSERT INTO lugar (nombre, ubicacion) VALUES('En Cayma 2', st_geomFromText('POINT(-71.5464019775391 -16.3690624625856)', 4326));
INSERT INTO lugar (nombre, ubicacion) VALUES('En Yanahuara', st_geomFromText('POINT(-71.5438270568848 -16.3927782959063)', 4326));
INSERT INTO lugar (nombre, ubicacion) VALUES('En Cercado', st_geomFromText('POINT(-71.5244293212891 -16.392448929097)', 4326));

-- Obtener los lugares dentro de Cayma
-- SELECT ...