2

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

  • Can you show a specific scenario? In particular, show the spatialite table layout and the shapefile content (headers and a few sample rows), and how you'd like them to match up if non-obvious. – BradHards Apr 21 '14 at 21:00
  • My spatialite table has three columns (city, state, country). The shapefiles have the previous columns, but also many other columns. I was thinking that my Append would have a SELECT clause in it (ie: SELECT city, state, country FROM shapefile...) – Ryan Garnett Apr 21 '14 at 21:06
  • Please edit the question, showing the table schema. – BradHards Apr 21 '14 at 21:07
  • Do you only want those columns from the shapefile? Which of city, state, country is the geometry column in the spatialite table? – BradHards Apr 21 '14 at 21:47
  • Yes, but the geometry is in a column called geom. City, state, and country are all text fields. – Ryan Garnett Apr 21 '14 at 21:55
3

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.

  • Thank you Brad, I wasn't sure if I needed to make a Virtual table, or if I could import (append) directly into my spatialite table from a shapefile, and limit the columns on the import. – Ryan Garnett Apr 21 '14 at 22:47
  • As I mentioned (but didn't describe) in my answer, there are other ways to do this. You can use .loadshp in the spatialite command line application, but that won't filter and won't be much faster - you'll still need to have another table. You can also use ogr2ogr from gdal, but I recommend the VirtualShape option because its available if you need to convert this to some other code format (rather than just running SQL in the spatialite CLI). – BradHards Apr 21 '14 at 22:56
  • Wish I could give you more rep for this! Great Answer! This is especially useful when creating a table with constraints while wanting to import a shape file into the created table (.loadshp does not support some constraints). – jsmith Feb 10 '15 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.