Skip to content

Instantly share code, notes, and snippets.

@yeesian

yeesian/spatialite-cookbook.ipynb

Created March 29, 2018 06:23
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yeesian/3ff9d5a4d0c6c16aa0b5ce4cd3b2a8ac to your computer and use it in GitHub Desktop.
Save yeesian/3ff9d5a4d0c6c16aa0b5ce4cd3b2a8ac to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
spatialite-cookbook.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction\n",
"\n",
"Starting with OGR 1.10, the SQLite SQL engine can be used to run SQL queries on any OGR datasource if using the SQLite SQL dialect.\n",
"\n",
"This notebook documents how you might go about doing so, and follows the cookbook in https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"ArchGDAL"
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import ArchGDAL; const AG = ArchGDAL"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## SQLite / Spatialite RDBMS\n",
"(taken from http://gdal.org/drv_sqlite.html)\n",
"\n",
"OGR optionally supports spatial and non-spatial tables stored in SQLite 3.x database files. SQLite is a \"light weight\" single file based RDBMS engine with fairly complete SQL semantics and respectable performance.\n",
"\n",
"The driver can handle \"regular\" SQLite databases, as well as Spatialite databases (spatial enabled SQLite databases). The type of an existing database can be checked from the SQLITE debug info value \"OGR style SQLite DB found/ SpatiaLite DB found/SpatiaLite v4 DB found\" obtained by running \"ogrinfo db.sqlite --debug on\"\n",
"\n",
"By default, SQL statements are passed directly to the SQLite database engine. It's also possible to request the driver to handle SQL commands with OGR SQL engine, by passing \"OGRSQL\" string to the ExecuteSQL() method, as name of the SQL dialect.\n",
"\n",
"### \"Regular\" SQLite databases\n",
"\n",
"The driver looks for a geometry_columns table laid out as defined loosely according to OGC Simple Features standards, particularly as defined in [FDO RFC 16](https://trac.osgeo.org/fdo/wiki/FDORfc16). If found it is used to map tables to layers.\n",
"\n",
"If `geometry_columns` is not found, each table is treated as a layer. Layers with a `WKT_GEOMETRY` field will be treated as spatial tables, and the `WKT_GEOMETRY` column will be read as Well Known Text geometry.\n",
"\n",
"If `geometry_columns` is found, it will be used to lookup spatial reference systems in the `spatial_ref_sys` table.\n",
"\n",
"While the SQLite driver supports reading spatial data from records, there is no support for spatial indexing, so spatial queries will tend to be slow (use Spatialite for that). Attributes queries may be fast, especially if indexes are built for appropriate attribute columns using the `\"CREATE INDEX ON ( )\"` SQL command.\n",
"\n",
"Starting with GDAL 2.0, the driver also supports reading and writing the following non-linear geometry types: `CIRCULARSTRING`, `COMPOUNDCURVE`, `CURVEPOLYGON`, `MULTICURVE` and `MULTISURFACE`. Note: this is not true for Spatialite databases, since those geometry types are not supported by current Spatialite versions.\n",
"\n",
"### Using the SpatiaLite library (Spatial extension for SQLite)\n",
"(Starting with GDAL 1.7.0)\n",
"\n",
"The SQLite driver can read and write SpatiaLite databases. Creating or updating a spatialite database requires explicit linking against SpatiaLite library (version >= 2.3.1). Explicit linking against SpatiaLite library also provides access to functions provided by this library, such as spatial indexes, spatial functions, etc...\n",
"\n",
"### Other Remarks\n",
"The SQLite database is essentially typeless, but the SQLite driver will attempt to classify attributes field as text, integer or floating point based on the contents of the first record in a table. None of the list attribute field types existing in SQLite. Starting with OGR 1.10, datetime field types are also handled.\n",
"\n",
"SQLite databases often due not work well over NFS, or some other networked file system protocols due to the poor support for locking. It is safest to operate only on SQLite files on a physical disk of the local system."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Preliminaries\n",
"\n",
"## Datasets\n",
"\n",
"The first dataset we'll use is the Italian National Census 2001, kindly released by ISTAT\t(the Italian Census Bureau). In particular, we'll be working with the following files (from `test/spatialite-cookbook`):\n",
"- Censimento 2001 - Regioni (Regions):\n",
"http://www3.istat.it/ambiente/cartografia/regioni2001.zip\n",
"- Censimento 2001 - Province (Counties):\n",
"http://www3.istat.it/ambiente/cartografia/province2001.zip\n",
"- Censimento 2001 - Comuni (Local Councils):\n",
"http://www3.istat.it/ambiente/cartografia/comuni2001.zip\n",
"\n",
"The second required dataset is GeoNames, a worldwide collection of Populated Places. There are several flavors of this dataset. We'll use cities-1000 (any populated place into the word counting more than 1,000 peoples): http://download.geonames.org/export/dump/cities1000.zip\n",
"\n",
"The files can be found in:"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"cities1000.txt\n",
"cities1000.zip\n",
"comuni2001\n",
"comuni2001.zip\n",
"cookbook-data.zip\n",
"province2001\n",
"regioni2001\n"
]
}
],
"source": [
";ls ../test/spatialite-cookbook/"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## VSI Virtual File System API support\n",
"\n",
"The driver supports reading and writing to files managed by VSI Virtual File System API, which include \"regular\" files, as well as files in the /vsimem/ (read-write), /vsizip/ (read-only), /vsigzip/ (read-only), /vsicurl/ (read-only) domains.\n",
"\n",
"read https://trac.osgeo.org/gdal/wiki/UserDocs/ReadInZip for more"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"GDAL Dataset (Driver: ESRI Shapefile/ESRI Shapefile)\n",
"Files: /vsizip/../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.shp\n",
" /vsizip/../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.shx\n",
" /vsizip/../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.dbf\n",
" /vsizip/../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.prj\n",
" /vsizip/../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.sbn\n",
" /vsizip/../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.sbx\n",
"\n",
"Number of feature layers: 1\n",
" Layer 0: com2001_s (wkbPolygon), nfeatures = 8101\n"
]
}
],
"source": [
"AG.registerdrivers() do\n",
" filepath = \"../test/spatialite-cookbook/cookbook-data.zip\"\n",
" AG.read(\"/vsizip/$filepath/comuni2001/com2001_s.shp\") do dataset\n",
" print(dataset)\n",
" end\n",
"end;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## The VirtualOGR SQLite extension\n",
"\n",
"Starting with OGR 1.10, the GDAL/OGR library can be loaded as a SQLite extension. This is automatically done when using SQLite through GDAL/OGR. After the extension is loaded, a virtual table, corresponding to a OGR layer, can be created with any one of the following SQL statements:\n",
"\n",
" CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name);\n",
" CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode);\n",
" CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode, layer_name);\n",
" CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode, layer_name, expose_ogr_style);\n",
"\n",
"where\n",
"\n",
"- `datasource_name` is the connection string to any OGR datasource.\n",
"- `update_mode` = `0` for read-only mode (default value) or `1` for update mode.\n",
"- `layer_name` = the name of a layer of the opened datasource.\n",
"- `expose_ogr_style` = `0` to prevent the OGR_STYLE special from being displayed (default value) or `1` to expose it.\n",
"\n",
"**Note**: layer_name does not need to be specified if the datasource has only one single layer."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"GDAL Dataset (Driver: SQLite/SQLite / Spatialite)\n",
"\n",
"Number of feature layers: 1\n",
" Layer 0: com2001 (wkbPolygon), nfeatures = 8101\n"
]
}
],
"source": [
"AG.registerdrivers() do\n",
" AG.create(\"\",\"SQLite\") do ds\n",
" filename = \"../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.shp\"\n",
" AG.unsafe_executesql(ds, \"CREATE VIRTUAL TABLE com2001 USING VirtualOGR('/vsizip/$filename')\")\n",
" print(ds)\n",
" end\n",
"end"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Alternatively, you can use the `ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]])` function to automatically load all the layers of a datasource. For example:\n",
"\n",
" SELECT ogr_datasource_load_layers('filepath')\n",
" SELECT * FROM sqlite_master"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 4\n",
" Field 0 (type): [OFTString], table, table, index, table\n",
" Field 1 (name): [OFTString], geometry_columns, spatial_ref_sys, ...\n",
" Field 2 (tbl_name): [OFTString], geometry_columns, spatial_ref_sys, ...\n",
" Field 3 (rootpage): [OFTInteger], 2, 3, 4, 0\n",
" Field 4 (sql): [OFTString], CREATE TABLE geometr..., ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"AG.registerdrivers() do\n",
" AG.create(\"\",\"SQLite\") do ds\n",
" filename = \"../test/spatialite-cookbook/cookbook-data.zip/comuni2001/com2001_s.shp\"\n",
" AG.unsafe_executesql(ds, \"SELECT ogr_datasource_load_layers('/vsizip/$filename')\")\n",
" AG.executesql(ds, \"SELECT * FROM sqlite_master\") do result\n",
" print(result)\n",
" end\n",
" end\n",
"end"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Opening with 'VirtualShape:'\n",
"\n",
"It is possible to open on-the-fly a shapefile as a VirtualShape with Spatialite. The syntax to use for the datasource is \"VirtualShape:/path/to/shapefile.shp\" (the shapefile must be a \"real\" file)."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"GDAL Dataset (Driver: SQLite/SQLite / Spatialite)\n",
"\n",
"Number of feature layers: 1\n",
" Layer 0: com2001_s (wkbMultiPolygon), nfeatures = 8101\n"
]
}
],
"source": [
"AG.registerdrivers() do\n",
" AG.read(\"VirtualShape:../test/spatialite-cookbook/comuni2001/com2001_s.shp\") do dataset\n",
" print(dataset)\n",
" end\n",
"end;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"read http://gdal.org/drv_sqlite.html for more."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Spatialite Cookbook\n",
"starts here. If this is your first introduction to working with SQL, I highly recommend you read the whole cookbook at https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/ as well."
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"inspect (generic function with 1 method)"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"function inspect(sqlcommand)\n",
" AG.registerdrivers() do\n",
" AG.create(\"\",\"SQLite\", options=[\"SPATIALITE=YES\"]) do ds\n",
" filepath = \"/vsizip/../test/spatialite-cookbook/cookbook-data.zip\"\n",
" for (layername, filename) in (\n",
" (\"com2001_s\", \"/comuni2001/com2001_s.shp\"),\n",
" (\"prov2001_s\", \"/province2001/prov2001_s.shp\"),\n",
" (\"reg2001_s\", \"/regioni2001/reg2001_s.shp\"))\n",
" sql = \"CREATE VIRTUAL TABLE $layername USING VirtualOGR('$filepath$filename')\"\n",
" AG.unsafe_executesql(ds, sql)\n",
" end\n",
" AG.executesql(ds, sqlcommand) do result\n",
" print(result)\n",
" end\n",
" end\n",
" end\n",
"end"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Geometry 0 (GEOMETRY): [wkbUnknown], POLYGON ((457832.312...), ...\n",
" Field 0 (COD_REG): [OFTInteger], 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, ...\n",
" Field 1 (REGIONE): [OFTString], PIEMONTE, VALLE D'AOSTA, LOMBARDIA, ...\n",
" Field 2 (POP2001): [OFTInteger], 4214677, 119548, 9032554, 940016, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT * \n",
"FROM reg2001_s\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (POP2001): [OFTInteger], 4214677, 119548, 9032554, 940016, ...\n",
" Field 1 (REGIONE): [OFTString], PIEMONTE, VALLE D'AOSTA, LOMBARDIA, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT pop2001, regione\n",
"FROM reg2001_s\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (code): [OFTInteger], 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, ...\n",
" Field 1 (name): [OFTString], PIEMONTE, VALLE D'AOSTA, LOMBARDIA, ...\n",
" Field 2 (population (2001)): [OFTInteger], 4214677, 119548, 9032554, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT Cod_rEg AS code, REGIONE AS name,\n",
" pop2001 AS \"population (2001)\"\n",
"FROM reg2001_s\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (COD_REG): [OFTInteger], 13, 17, 18, 15, 8, 6, 12, 7, 3, 11, ...\n",
" Field 1 (REGIONE): [OFTString], ABRUZZO, BASILICATA, CALABRIA, ...\n",
" Field 2 (POP2001): [OFTInteger], 1262392, 597768, 2011466, 5701931, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_REG, REGIONE, POP2001\n",
"FROM reg2001_s\n",
"ORDER BY regione\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (COD_REG): [OFTInteger], 3, 15, 12, 19, 5, 1, 16, 8, 9, 18, 20, ...\n",
" Field 1 (REGIONE): [OFTString], LOMBARDIA, CAMPANIA, LAZIO, SICILIA, ...\n",
" Field 2 (POP2001): [OFTInteger], 9032554, 5701931, 5112413, 4968991, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_REG, REGIONE, POP2001\n",
"FROM reg2001_s\n",
"ORDER BY POP2001 DESC\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 10\n",
" Field 0 (COD_PRO): [OFTInteger], 45, 46, 47, 48, 49, 50, 51, 52, 53, ...\n",
" Field 1 (PROVINCIA): [OFTString], MASSA CARRARA, LUCCA, PISTOIA, ...\n",
" Field 2 (SIGLA): [OFTString], MS, LU, PT, FI, LI, PI, AR, SI, GR, PO\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_PRO, PROVINCIA, SIGLA\n",
"FROM prov2001_s\n",
"WHERE COD_REG = 9\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 44\n",
" Field 0 (PRO_COM): [OFTInteger], 48001, 48002, 48003, 48004, 48005, ...\n",
" Field 1 (NOME_COM): [OFTString], BAGNO A RIPOLI, BARBERINO DI MUGELLO, ...\n",
" Field 2 (POP2001): [OFTInteger], 25232, 9531, 3871, 15825, 15042, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE COD_PRO = 48\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 13\n",
" Field 0 (PRO_COM): [OFTInteger], 48017, 100005, 49009, 51002, 50026, ...\n",
" Field 1 (NOME_COM): [OFTString], FIRENZE, PRATO, LIVORNO, AREZZO, PISA, ...\n",
" Field 2 (POP2001): [OFTInteger], 356118, 172499, 156274, 91589, 89694, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE COD_REG = 9 AND POP2001 > 50000\n",
"ORDER BY POP2001 DESC\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (PRO_COM): [OFTInteger], 58091\n",
" Field 1 (NOME_COM): [OFTString], ROMA\n",
" Field 2 (POP2001): [OFTInteger], 2546804\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE nome_com = 'ROMA'\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (PRO_COM): [OFTInteger], 66049\n",
" Field 1 (NOME_COM): [OFTString], L'AQUILA\n",
" Field 2 (POP2001): [OFTInteger], 68503\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE nome_com = 'L''AQUILA'\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (PRO_COM): [OFTInteger], 58091\n",
" Field 1 (NOME_COM): [OFTString], ROMA\n",
" Field 2 (POP2001): [OFTInteger], 2546804\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE nome_com LIKE 'roma'\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 27\n",
" Field 0 (PRO_COM): [OFTInteger], 13143, 13210, 14067, 18143, 20032, ...\n",
" Field 1 (NOME_COM): [OFTString], MARIANO COMENSE, ...\n",
" Field 2 (POP2001): [OFTInteger], 20282, 1088, 892, 2584, 594, 13685, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE nome_com LIKE '%maria%'\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 3\n",
" Field 0 (PRO_COM): [OFTInteger], 15146, 58091, 63049\n",
" Field 1 (NOME_COM): [OFTString], MILANO, ROMA, NAPOLI\n",
" Field 2 (POP2001): [OFTInteger], 1256211, 2546804, 1004500\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE nome_com IN ('ROMA', 'MILANO', 'NAPOLI')\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 26\n",
" Field 0 (PRO_COM): [OFTInteger], 1027, 1222, 5012, 5087, 8045, 9068, ...\n",
" Field 1 (NOME_COM): [OFTString], BOLLENGO, ROLETTO, BUTTIGLIERA D'ASTI, ...\n",
" Field 2 (POP2001): [OFTInteger], 1997, 1994, 1996, 1992, 2002, 1991, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PRO_COM, NOME_COM, POP2001\n",
"FROM com2001_s\n",
"WHERE POP2001 BETWEEN 1990 AND 2010\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 10\n",
" Field 0 (PROVINCIA): [OFTString], LUCCA, FIRENZE, AREZZO, ...\n",
" Field 1 (SIGLA): [OFTString], LU, FI, AR, PS, AN, MC, AP, RM, LT, FR\n",
" Field 2 (POP2001): [OFTInteger], 372244, 933860, 323288, 351214, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT PROVINCIA, SIGLA, POP2001\n",
"FROM prov2001_s\n",
"WHERE COD_REG IN (9, 10, 11, 12)\n",
" AND SIGLA NOT IN ('LI', 'PI')\n",
" AND (POP2001 BETWEEN 300000 AND 500000\n",
" OR POP2001 > 750000)\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 10\n",
" Geometry 0 (GEOMETRY): [wkbUnknown], POLYGON ((405246.812...), ...\n",
" Field 0 (PRO_COM): [OFTInteger], 1001, 1002, 1003, 1004, 1005, 1006, ...\n",
" Field 1 (COD_REG): [OFTInteger], 1, 1, 1, 1, 1, 1, 1, 1, 1, 1\n",
" Field 2 (COD_PRO): [OFTInteger], 1, 1, 1, 1, 1, 1, 1, 1, 1, 1\n",
" Field 3 (NOME_COM): [OFTString], AGLIE', AIRASCA, ALA DI STURA, ...\n",
" Field 4 (POP2001): [OFTInteger], 2574, 3554, 479, 1696, 616, 5658, 300, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT *\n",
"FROM com2001_s\n",
"LIMIT 10\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 10\n",
" Geometry 0 (GEOMETRY): [wkbUnknown], MULTIPOLYGON (((4671...), ...\n",
" Field 0 (PRO_COM): [OFTInteger], 6144, 6145, 6146, 6147, 6148, 6149, ...\n",
" Field 1 (COD_REG): [OFTInteger], 1, 1, 1, 1, 1, 1, 1, 1, 1, 1\n",
" Field 2 (COD_PRO): [OFTInteger], 6, 6, 6, 6, 6, 6, 6, 6, 6, 6\n",
" Field 3 (NOME_COM): [OFTString], RIVALTA BORMIDA, RIVARONE, ...\n",
" Field 4 (POP2001): [OFTInteger], 1443, 372, 167, 1346, 220, 1650, 475, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT *\n",
"FROM com2001_s\n",
"LIMIT 10 OFFSET 1000\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Understanding Aggregate Functions"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (Min(POP2001)): [OFTInteger], 33\n",
" Field 1 (Max(POP2001)): [OFTInteger], 2546804\n",
" Field 2 (Avg(POP2001)): [OFTReal], 7035.6430070361685\n",
" Field 3 (Sum(POP2001)): [OFTInteger], 56995744\n",
" Field 4 (Count(*)): [OFTInteger], 8101\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT Min(POP2001), Max(POP2001),\n",
" Avg(POP2001), Sum(POP2001), Count(*)\n",
"FROM com2001_s\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 103\n",
" Field 0 (COD_PRO): [OFTInteger], 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, ...\n",
" Field 1 (Min(POP2001)): [OFTInteger], 46, 49, 260, 56, 104, 95, 91, ...\n",
" Field 2 (Max(POP2001)): [OFTInteger], 865263, 45132, 100910, 52334, ...\n",
" Field 3 (Avg(POP2001)): [OFTReal], 6874.980952380953, ...\n",
" Field 4 (Sum(POP2001)): [OFTInteger], 2165619, 176829, 343040, 556330, ...\n",
"...\n",
" Number of Fields: 6"
]
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_PRO, Min(POP2001), Max(POP2001),\n",
" Avg(POP2001), Sum(POP2001), Count(*)\n",
"FROM com2001_s\n",
"GROUP BY COD_PRO\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (COD_REG): [OFTInteger], 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, ...\n",
" Field 1 (Min(POP2001)): [OFTInteger], 46, 91, 33, 105, 128, 195, 95, ...\n",
" Field 2 (Max(POP2001)): [OFTInteger], 865263, 34062, 1256211, 104946, ...\n",
" Field 3 (Avg(POP2001)): [OFTReal], 3494.757048092869, ...\n",
" Field 4 (Sum(POP2001)): [OFTInteger], 4214677, 119548, 9032554, 940016, ...\n",
"...\n",
" Number of Fields: 6"
]
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_REG, Min(POP2001), Max(POP2001),\n",
" Avg(POP2001), Sum(POP2001), Count(*)\n",
"FROM com2001_s\n",
"GROUP BY COD_REG\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 103\n",
" Field 0 (COD_REG): [OFTInteger], 1, 1, 1, 1, 1, 1, 1, 1, 2, 3, 3, 3, 3, ...\n",
" Field 1 (COD_PRO): [OFTInteger], 1, 2, 3, 4, 5, 6, 96, 103, 7, 12, 13, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT DISTINCT COD_REG, COD_PRO\n",
"FROM com2001_s\n",
"ORDER BY COD_REG, COD_PRO\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Your First SQL Spatial Queries"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (COD_REG): [OFTInteger], 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, ...\n",
" Field 1 (REGIONE): [OFTString], PIEMONTE, VALLE D'AOSTA, LOMBARDIA, ...\n",
" Field 2 (ST_Area(Geometry)): [OFTReal], 2.5395423847624672e10, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_REG, REGIONE, ST_Area(Geometry)\n",
"FROM reg2001_s\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (code): [OFTInteger], 19, 1, 20, 3, 9, 8, 16, 5, 12, 18, 15, 4, ...\n",
" Field 1 (name): [OFTString], SICILIA, PIEMONTE, SARDEGNA, LOMBARDIA, ...\n",
" Field 2 (Surface (sq.Km)): [OFTReal], 25735.673661027224, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_REG AS code,\n",
" REGIONE AS name,\n",
" ST_Area(Geometry) / 1000000.0 AS \"Surface (sq.Km)\"\n",
"FROM reg2001_s\n",
"ORDER BY 3 DESC\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 20\n",
" Field 0 (code): [OFTInteger], 15, 3, 12, 7, 5, 16, 19, 8, 1, 9, 11, 6, ...\n",
" Field 1 (name): [OFTString], CAMPANIA, LOMBARDIA, LAZIO, LIGURIA, ...\n",
" Field 2 (Surface (sq.Km)): [OFTReal], 13666.322145371321, ...\n",
" Field 3 (Density: Peoples / sq.Km): [OFTReal], 417.22498118714407, ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT COD_REG AS code,\n",
" REGIONE AS name,\n",
" ST_Area(Geometry) / 1000000.0 AS \"Surface (sq.Km)\",\n",
" POP2001 / (ST_Area(Geometry) / 1000000.0)\n",
" AS \"Density: Peoples / sq.Km\"\n",
"FROM reg2001_s\n",
"ORDER BY 4 DESC\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### More about Spatial SQL: WKT and WKB"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (Hex(ST_GeomFromText('POINT(1.2345 2.3456)'))): [OFTString], ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT Hex(ST_GeomFromText('POINT(1.2345 2.3456)'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_AsText(x'0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE')): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_AsText(x'0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE')\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (Hex(ST_AsBinary(x'0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE'))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 34,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT Hex(ST_AsBinary(x'0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (Hex(ST_AsBinary(ST_GeomFromText('POINT(1.2345 2.3456)')))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT Hex(ST_AsBinary(ST_GeomFromText('POINT(1.2345 2.3456)')))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_AsText(ST_GeomFromWKB(x'01010000008D976E1283C0F33F16FBCBEEC9C30240'))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 36,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_AsText(ST_GeomFromWKB(x'01010000008D976E1283C0F33F16FBCBEEC9C30240'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_GeometryType(ST_GeomFromText('POINT(1.2345 2.3456)'))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_GeometryType(ST_GeomFromText('POINT(1.2345 2.3456)'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 38,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_GeometryType(ST_GeomFromText('POINTZ(1.2345 2.3456 10)'))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 38,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_GeometryType(ST_GeomFromText('POINTZ(1.2345 2.3456 10)'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_GeometryType(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)'))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_GeometryType(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)'))): [OFTInteger][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 40,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)', 4326))): [OFTInteger][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 41,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)', 4326))\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Common PitFalls"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_GeometryType(ST_GeomFromText('MULTIPOINT(1.2345 2.3456)'))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 42,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_GeometryType(ST_GeomFromText('MULTIPOINT(1.2345 2.3456)'))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 43,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_AsText(CastToMultiLineString(ST_GeomFromText('LINESTRING(1.2345 2.3456, 12.3456 23.4567)')))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 43,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_AsText(CastToMultiLineString(ST_GeomFromText('LINESTRING(1.2345 2.3456, 12.3456 23.4567)')))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 44,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_AsText(CastToXYZM(ST_GeomFromText('POINT(1.2345 2.3456)')))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 44,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_AsText(CastToXYZM(ST_GeomFromText('POINT(1.2345 2.3456)')))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (ST_AsText(CastToXY(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)')))): [OFTString][1:70]...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 45,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT ST_AsText(CastToXY(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)')))\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 46,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 1\n",
" Field 0 (Hex(ST_GeomFromText('POINT(1.2345 2.3456)'))): [OFTString], ...\n"
]
},
{
"data": {
"text/plain": [
"false"
]
},
"execution_count": 46,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT Hex(ST_GeomFromText('POINT(1.2345 2.3456)'))\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Spatial MetaData Tables"
]
},
{
"cell_type": "code",
"execution_count": 47,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = -1\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"InitSpatiaMetaData() error:\"table spatial_ref_sys already exists\"\n"
]
},
{
"ename": "LoadError",
"evalue": "LoadError: GDALError\n\tGDAL returned nothing\nwhile loading In[47], in expression starting on line 1",
"output_type": "error",
"traceback": [
"LoadError: GDALError\n\tGDAL returned nothing\nwhile loading In[47], in expression starting on line 1",
"",
" in show at /Users/yeesian/.julia/v0.4/ArchGDAL/src/base/display.jl:99",
" in print at strings/io.jl:8",
" in print at strings/io.jl:27",
" in anonymous at In[7]:13",
" in executesql at /Users/yeesian/.julia/v0.4/ArchGDAL/src/context.jl:35",
" in anonymous at In[7]:12",
" in create at /Users/yeesian/.julia/v0.4/ArchGDAL/src/context.jl:72",
" in anonymous at In[7]:3",
" in registerdrivers at /Users/yeesian/.julia/v0.4/ArchGDAL/src/context.jl:12",
" in inspect at In[7]:2"
]
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT InitSpatialMetaData()\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 48,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 4924\n",
" Field 0 (srid): [OFTInteger], -1, 0, 2000, 2001, 2002, 2003, 2004, ...\n",
" Field 1 (auth_name): [OFTString], NONE, NONE, epsg, epsg, epsg, epsg, ...\n",
" Field 2 (auth_srid): [OFTInteger], -1, 0, 2000, 2001, 2002, 2003, 2004, ...\n",
" Field 3 (ref_sys_name): [OFTString], Undefined - Cartesian, ...\n",
" Field 4 (proj4text): [OFTString], , , +proj=tmerc +lat_0=0..., ...\n",
"...\n",
" Number of Fields: 6"
]
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT * FROM spatial_ref_sys\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 49,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Layer: SELECT, nfeatures = 0\n",
" Field 0 (f_table_name): [OFTString]\n",
" Field 1 (f_geometry_column): [OFTString]\n",
" Field 2 (geometry_type): [OFTInteger]\n",
" Field 3 (coord_dimension): [OFTInteger]\n",
" Field 4 (srid): [OFTInteger]\n",
"...\n",
" Number of Fields: 6"
]
}
],
"source": [
"inspect(\"\"\"\n",
"SELECT * FROM geometry_columns\n",
"\"\"\")"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Julia 0.4.6",
"language": "julia",
"name": "julia-0.4"
},
"language_info": {
"file_extension": ".jl",
"mimetype": "application/julia",
"name": "julia",
"version": "0.4.6"
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment