spatialite find if point is in polygon

How to find if a point is in a polygon. The shape file I used is tigerline data from the census site. I created a new database and imported the shapefile.

$ spatialite us.sqlite
SpatiaLite version ..: 4.1.1    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]
    - 'VirtualXPath'    [XML Path Language - XPath]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
SQLite version ......: 3.8.2
Enter ".help" for instructions
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
spatialite> .loadshp tl_2013_us_state states CP1252
========
Loading shapefile at 'tl_2013_us_state' into SQLite table 'states'
 
BEGIN;
CREATE TABLE "states" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"REGION" TEXT,
"DIVISION" TEXT,
"STATEFP" TEXT,
"STATENS" TEXT,
"GEOID" TEXT,
"STUSPS" TEXT,
"NAME" TEXT,
"LSAD" TEXT,
"MTFCC" TEXT,
"FUNCSTAT" TEXT,
"ALAND" INTEGER,
"AWATER" INTEGER,
"INTPTLAT" TEXT,
"INTPTLON" TEXT);
SELECT AddGeometryColumn('states', 'Geometry', -1, 'MULTIPOLYGON', 'XY');
COMMIT;
 
Inserted 56 rows into 'states' from SHAPEFILE
========
spatialite> select name from states where within(GeomFromText('POINT(-97.74342 30.26771)'),states.Geometry);
Texas
spatialite>

Lets look at the select statement closer, specifically the where clause:

SELECT name FROM states WHERE within(GeomFromText('POINT(-97.74342 30.26771)'),states.Geometry);

The .loadshp command was used to load the data into a table named states.

In the where clause within is a function that tests if the first argument is inside the second argument. In this case the first argument is a point.

The function GeomFromText creates a geometric object from a string. The point says it is a point and you give it coordinates longitude latitude. There is also a PointFromText function and it looks the exact same except instead of GeomFromText the function is PointFromText

select name from states where within(PointFromText('POINT(-97.74342 30.26771)'),states.Geometry);

I don’t get the point (hah).

The second argument to the within function is in this case the geometry column for the states table, I think. I really do not know how this works though.

The spatialite function list has a bunch of useful stuff.