[GIS] Import Shapefile into Existing Spatialite Table

shapefilespatialite

I am working with a Spatialite database and a series of shapefiles. I have an empty spatialite table that has geometry and columns. What I would like to do is to load the shapefiles into the spatialite table (Append) but I am unsure if this is possible with Spatialite. I do not see an option with the Spatialite-GUI, or the QGIS plugin. Does anyone have any ideas or tricks the a SQL query?

Thanks…

EDIT

I would like to add a SELECT statement so that I only get selected rows

Best Answer

So given a table with four explicit columns - City, State and Country, plus geom:

spatialite> CREATE TABLE MyPlaces (Country TEXT, State TEXT, City TEXT);
spatialite> SELECT AddGeometryColumn("MyPlaces", "geom", 4326, 'POINT', 'XY');
AddGeometryColumn("MyPlaces", "geom", 4326, 'POINT', 'XY')
1

We want to take a source of places (I used Natural Earth, 1:50m, because it was already handy from another question).

We can do the import in a range of ways, but a Spatialite Virtual Table (supporting shapefile) is among the easiest.

spatialite> CREATE VIRTUAL TABLE ne50pp USING VirtualShape("ne_50m_populated_places_simple", "CP1251", 4326);

Lets see what is now available:

spatialite> .headers on
spatialite> SELECT * FROM ne50pp LIMIT 5;
PKUID|Geometry|scalerank|natscale|labelrank|featurecla|name|namepar|namealt|diffascii|nameascii|adm0cap|capalt|capin|worldcity|megacity|sov0name|sov_a3|adm0name|adm0_a3|adm1name|iso_a2|note|latitude|longitude|changed|namediff|diffnote|pop_max|pop_min|pop_other|rank_max|rank_min|geonameid|meganame|ls_name|ls_match|checkme
1||10|1|5|Admin-1 region capital|Bombo|||0|Bombo|0.0|0.0||0.0|0|Uganda|UGA|Uganda|UGA|Bamunanika|UG||0.58329910562|32.5332995249|4.0|1|Added missing admin-1 capital. Changed feature to Admin-0 region capital.|75000|21000|0|8|7|-1.0|||0|0
2||10|1|5|Admin-1 region capital|Fort Portal|||0|Fort Portal|0.0|0.0||0.0|0|Uganda|UGA|Uganda|UGA|Kabarole|UG||0.67100412113|30.275001616|4.0|1|Added missing admin-1 capital. Changed feature to Admin-0 region capital. Population from GeoNames.|42670|42670|0|7|7|233476.0|||0|0
3||10|1|3|Admin-1 region capital|Potenza|||0|Potenza|0.0|0.0||0.0|0|Italy|ITA|Italy|ITA|Basilicata|IT||40.6420021301|15.7989964956|4.0|1|Added missing admin-1 capital. Population from GeoNames.|69060|69060|0|8|8|3170027.0|||0|0
4||10|1|3|Admin-1 region capital|Campobasso|||0|Campobasso|0.0|0.0||0.0|0|Italy|ITA|Italy|ITA|Molise|IT||41.5629991186|14.6559965589|4.0|1|Added missing admin-1 capital. Population from GeoNames.|50762|50762|0|8|8|3180991.0|||0|0
5||10|1|3|Admin-1 region capital|Aosta|||0|Aosta|0.0|0.0||0.0|0|Italy|ITA|Italy|ITA|Valle d'Aosta|IT||45.7370010671|7.31500259571|4.0|1|Added missing admin-1 capital. Population from GeoNames. Changed feature class.|34062|34062|0|7|7|3182997.0|||0|0

OK, but we don't want all of those columns. Country and state are ambiguous, but I'll assume you meant something like administrative regions:

spatialite> SELECT adm0name,adm1name,name, AsText(Geometry) FROM ne50pp LIMIT 5;
adm0name|adm1name|name|AsText(Geometry)
Uganda|Bamunanika|Bombo|POINT(32.5333 0.583299)
Uganda|Kabarole|Fort Portal|POINT(30.275002 0.671004)
Italy|Basilicata|Potenza|POINT(15.798996 40.642002)
Italy|Molise|Campobasso|POINT(14.655997 41.562999)
Italy|Valle d'Aosta|Aosta|POINT(7.315003 45.737001)

We can also select only a subset of those cities (say, all the places where the megacity column value is 1):

spatialite> SELECT adm0name,adm1name,name, megacity, AsText(Geometry) FROM ne50pp WHERE megacity=1 LIMIT 5;
adm0name|adm1name|name|megacity|AsText(Geometry)
Italy|Piemonte|Turin|1|POINT(7.668015 45.072333)
France|Midi-Pyrйnйes|Toulouse|1|POINT(1.447981 43.621905)
France|Nord-Pas-de-Calais|Lille|1|POINT(3.078062 50.651915)
United States of America|California|San Bernardino|1|POINT(-117.30198 34.12233)
United States of America|Connecticut|Bridgeport|1|POINT(-73.201907 41.181925)

Now to import that subset of places into our table, we use a SQLite INSERT INTO command:

spatialite> INSERT INTO MyPlaces (Country, State, City, geom) SELECT adm0name, adm1name, name, Geometry FROM ne50pp WHERE megacity = 1;

Now we can check the results:

spatialite> SELECT Country, State, City, AsText(geom) FROM MyPlaces LIMIT 10;
Country|State|City|AsText(geom)
Italy|Piemonte|Turin|POINT(7.668015 45.072333)
France|Midi-Pyrйnйes|Toulouse|POINT(1.447981 43.621905)
France|Nord-Pas-de-Calais|Lille|POINT(3.078062 50.651915)
United States of America|California|San Bernardino|POINT(-117.30198 34.12233)
United States of America|Connecticut|Bridgeport|POINT(-73.201907 41.181925)
United States of America|New York|Rochester|POINT(-77.621896 43.172371)
United Kingdom|Manchester|Manchester|POINT(-2.249933 53.502361)
Pakistan|Punjab|Gujranwala|POINT(74.183076 32.162372)
South Korea|Inch'on-gwangyoksi|Incheon|POINT(126.640288 37.478094)
Nigeria|Edo|Benin City|POINT(5.618062 6.342423)

Obviously the WHERE could be any criteria you need.

Related Question