I'm working on a school project involving Spatialite and point in polygon queries.I've successfully followed this example:http://blog.crazystyle.net/?p=255
I am now trying the same sequence of steps with the shapefiles for “Travis and Williamson Counties Karst Zones and Critical Salamander Habitat” downloadable from here and viewable online here. I have manually determined that the GPS point 30.418986, -97.83754 should intersect the polygons Jollyville Plateau salamander subsurf crit. hab., karst zone 3, and Jollyville Plateau salamander surface crit. hab.
However, I cannot get the SQL query I'm running to return the data I expect. Please see the transcript below for the query syntax.
Here's a transcript of my spatialite work:
spatialite> spatialite karst.sqliteSpatiaLite version ..: 4.3.0 Supported Extensions: - 'VirtualShape' [direct Shapefile access] - 'VirtualDbf' [direct DBF access] - 'VirtualXL' [direct XLS access] - 'VirtualText' [direct CSV/TXT access] - 'VirtualNetwork' [Dijkstra shortest path] - 'RTree' [Spatial Index - R*Tree] - 'MbrCache' [Spatial Index - MBR cache] - 'VirtualSpatialIndex' [R*Tree metahandler] - 'VirtualElementary' [ElemGeoms metahandler] - 'VirtualXPath' [XML Path Language - XPath] - 'VirtualFDO' [FDO-OGR interoperability] - 'VirtualGPKG' [OGC GeoPackage interoperability] - 'VirtualBBox' [BoundingBox tables] - 'SpatiaLite' [Spatial SQL - OGC]PROJ.4 version ......: Rel. 4.9.1, 04 March 2015GEOS version ........: 3.4.2-CAPI-1.8.2 r3921LWGEOM version ......: 2.1.8TARGET CPU ..........: x86_64-linux-gnuthe SPATIAL_REF_SYS table already contains some row(s)SQLite version ......: 3.8.11.1Enter ".help" for instructionsSQLite version 3.8.11.1 2015-07-29 20:00:57Enter ".help" for instructionsEnter SQL statements terminated with a ";"spatialite> .loadshp /mnt/Trav_Will_KarstZones_Veni_07 zones CP1252 26914========Loading shapefile at '/mnt/Trav_Will_KarstZones_Veni_07' into SQLite table 'zones'BEGIN;CREATE TABLE "zones" ("PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,"OBJECTID" INTEGER,"ZONE_" INTEGER,"ZONE_DESC" TEXT,"LABEL" TEXT,"MAP_ID" DOUBLE,"Area_km2" DOUBLE,"Shape_Leng" DOUBLE,"Shape_Area" DOUBLE);SELECT AddGeometryColumn('zones', 'Geometry', 26914, 'MULTIPOLYGON', 'XY');COMMIT;Inserted 310 rows into 'zones' from SHAPEFILEspatialite> select * from zones where within(GeomFromText('POINT(-97.837543 30.418986)'),zones.Geometry);spatialite>
أكثر...
I am now trying the same sequence of steps with the shapefiles for “Travis and Williamson Counties Karst Zones and Critical Salamander Habitat” downloadable from here and viewable online here. I have manually determined that the GPS point 30.418986, -97.83754 should intersect the polygons Jollyville Plateau salamander subsurf crit. hab., karst zone 3, and Jollyville Plateau salamander surface crit. hab.
However, I cannot get the SQL query I'm running to return the data I expect. Please see the transcript below for the query syntax.
- Am I running the correct query for this set of data?
- Does the shapefile have the info I need for this sort of query? I notice the column names for this data are different from the column names in the example census data table.
Here's a transcript of my spatialite work:
spatialite> spatialite karst.sqliteSpatiaLite version ..: 4.3.0 Supported Extensions: - 'VirtualShape' [direct Shapefile access] - 'VirtualDbf' [direct DBF access] - 'VirtualXL' [direct XLS access] - 'VirtualText' [direct CSV/TXT access] - 'VirtualNetwork' [Dijkstra shortest path] - 'RTree' [Spatial Index - R*Tree] - 'MbrCache' [Spatial Index - MBR cache] - 'VirtualSpatialIndex' [R*Tree metahandler] - 'VirtualElementary' [ElemGeoms metahandler] - 'VirtualXPath' [XML Path Language - XPath] - 'VirtualFDO' [FDO-OGR interoperability] - 'VirtualGPKG' [OGC GeoPackage interoperability] - 'VirtualBBox' [BoundingBox tables] - 'SpatiaLite' [Spatial SQL - OGC]PROJ.4 version ......: Rel. 4.9.1, 04 March 2015GEOS version ........: 3.4.2-CAPI-1.8.2 r3921LWGEOM version ......: 2.1.8TARGET CPU ..........: x86_64-linux-gnuthe SPATIAL_REF_SYS table already contains some row(s)SQLite version ......: 3.8.11.1Enter ".help" for instructionsSQLite version 3.8.11.1 2015-07-29 20:00:57Enter ".help" for instructionsEnter SQL statements terminated with a ";"spatialite> .loadshp /mnt/Trav_Will_KarstZones_Veni_07 zones CP1252 26914========Loading shapefile at '/mnt/Trav_Will_KarstZones_Veni_07' into SQLite table 'zones'BEGIN;CREATE TABLE "zones" ("PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,"OBJECTID" INTEGER,"ZONE_" INTEGER,"ZONE_DESC" TEXT,"LABEL" TEXT,"MAP_ID" DOUBLE,"Area_km2" DOUBLE,"Shape_Leng" DOUBLE,"Shape_Area" DOUBLE);SELECT AddGeometryColumn('zones', 'Geometry', 26914, 'MULTIPOLYGON', 'XY');COMMIT;Inserted 310 rows into 'zones' from SHAPEFILEspatialite> select * from zones where within(GeomFromText('POINT(-97.837543 30.418986)'),zones.Geometry);spatialite>
أكثر...