map_suite_sqlite_guide
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
map_suite_sqlite_guide [2015/08/20 03:08] 127.0.0.1 external edit |
map_suite_sqlite_guide [2015/09/16 10:00] (current) admin [SQLite Dependencies] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Map_Suite_SQLite_Guide ====== | + | ====== Map Suite SQLite Guide ====== |
| - | + | ||
| - | {{article rating}} | + | |
| Map Suite now supports SQLite through an extension called SQLiteExtension, which is located with all the other core extensions. This page is designed to give you some insight into why we wanted to support SQLite, how to use it, and details on how we implemented it. We also provide SQL statements to help you in the event you decide to modify a database outside of our APIs. This guide highlights some of the functionality but is not a replacement for the API documentation which provides a list of every method, property, and class associated with SQLite. | Map Suite now supports SQLite through an extension called SQLiteExtension, which is located with all the other core extensions. This page is designed to give you some insight into why we wanted to support SQLite, how to use it, and details on how we implemented it. We also provide SQL statements to help you in the event you decide to modify a database outside of our APIs. This guide highlights some of the functionality but is not a replacement for the API documentation which provides a list of every method, property, and class associated with SQLite. | ||
| Line 25: | Line 23: | ||
| ===== SQLite APIs in Map Suite ===== | ===== SQLite APIs in Map Suite ===== | ||
| ==== Create a New Database ==== | ==== Create a New Database ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> You can use the static method on the SqliteFeatureSource to create a new database file. | + | // You can use the static method on the SqliteFeatureSource to create a new database file. |
| SqliteFeatureSource.CreateDatabase(@"C:\sampledata\austin_streets.sqlite"); | SqliteFeatureSource.CreateDatabase(@"C:\sampledata\austin_streets.sqlite"); | ||
| - | </source> | + | </code> |
| ==== Create a New Table ==== | ==== Create a New Table ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> We need to add an API to create a table and spatial index at once. | + | // We need to add an API to create a table and spatial index at once. |
| - | </source> | + | </code> |
| ==== Opening a Database ==== | ==== Opening a Database ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> We open the FeatureSource and pass in the database path along with the table name, id column and geometry column. | + | // We open the FeatureSource and pass in the database path along with the table name, id column and geometry column. |
| - | <nowiki>//</nowiki> The same code is used to open the SqliteFeatureLayer. | + | // The same code is used to open the SqliteFeatureLayer. |
| SqliteFeatureSource sqliteFeatureSource = new SqliteFeatureSource(@"Data Source=C:\sampledata\austin_streets.sqlite;Version=3;", "austin_streets", "id", "geometry"); | SqliteFeatureSource sqliteFeatureSource = new SqliteFeatureSource(@"Data Source=C:\sampledata\austin_streets.sqlite;Version=3;", "austin_streets", "id", "geometry"); | ||
| sqliteFeatureSource.Open(); | sqliteFeatureSource.Open(); | ||
| - | </source> | + | </code> |
| ==== Inserting New Records ==== | ==== Inserting New Records ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> Assuming the featuresource is opened, we begin a transaction, then add the new records and call the commit transaction. | + | // Assuming the featuresource is opened, we begin a transaction, then add the new records and call the commit transaction. |
| sqliteFeatureSource.BeginTransaction(); | sqliteFeatureSource.BeginTransaction(); | ||
| sqliteFeatureSource.AddFeature(new Feature(10,20,"1")); | sqliteFeatureSource.AddFeature(new Feature(10,20,"1")); | ||
| sqliteFeatureSource.AddFeature(new Feature(5,15,"2")); | sqliteFeatureSource.AddFeature(new Feature(5,15,"2")); | ||
| sqliteFeatureSource.CommitTransaction(); | sqliteFeatureSource.CommitTransaction(); | ||
| - | </source> | + | </code> |
| ==== Editing Records ==== | ==== Editing Records ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> Here we are updating feature 1 with a new set of coordinates. | + | // Here we are updating feature 1 with a new set of coordinates. |
| sqliteFeatureSource.BeginTransaction(); | sqliteFeatureSource.BeginTransaction(); | ||
| sqliteFeatureSource.UpdateFeature(new Feature(10,10,"1")); | sqliteFeatureSource.UpdateFeature(new Feature(10,10,"1")); | ||
| sqliteFeatureSource.CommitTransaction(); | sqliteFeatureSource.CommitTransaction(); | ||
| - | </source> | + | </code> |
| ==== Deleting Records ==== | ==== Deleting Records ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> Here we are deleting features 1 and 2. | + | // Here we are deleting features 1 and 2. |
| sqliteFeatureSource.BeginTransaction(); | sqliteFeatureSource.BeginTransaction(); | ||
| sqliteFeatureSource.DeleteFeature("1"); | sqliteFeatureSource.DeleteFeature("1"); | ||
| sqliteFeatureSource.DeleteFeature("2"); | sqliteFeatureSource.DeleteFeature("2"); | ||
| sqliteFeatureSource.CommitTransaction(); | sqliteFeatureSource.CommitTransaction(); | ||
| - | </source> | + | </code> |
| ==== Opening a SQLiteFeatureLayer For Display or Analysis ==== | ==== Opening a SQLiteFeatureLayer For Display or Analysis ==== | ||
| - | <source lang="csharp"> | + | <code csharp> |
| - | <nowiki>//</nowiki> We open the FeatureLayer and pass in the database path along with the table name, id column and geometry column. | + | // We open the FeatureLayer and pass in the database path along with the table name, id column and geometry column. |
| SqliteFeatureLayer sqliteFeatureLayer = new SqliteFeatureLayer(@"Data Source=C:\sampledata\austin_streets.sqlite;Version=3;", "austin_streets", "id", "geometry"); | SqliteFeatureLayer sqliteFeatureLayer = new SqliteFeatureLayer(@"Data Source=C:\sampledata\austin_streets.sqlite;Version=3;", "austin_streets", "id", "geometry"); | ||
| sqliteFeatureLayer.Open(); | sqliteFeatureLayer.Open(); | ||
| - | </source> | + | </code> |
| ===== Sample SQLite Scripts ===== | ===== Sample SQLite Scripts ===== | ||
| Line 80: | Line 78: | ||
| ==== Create a New Table ==== | ==== Create a New Table ==== | ||
| - | <source lang="sql"> | + | <code sql> |
| -- This creates a table for decimal degrees. Note the rtree part of the CREATE VIRTUAL TABLE | -- This creates a table for decimal degrees. Note the rtree part of the CREATE VIRTUAL TABLE | ||
| BEGIN TRANSACTION; | BEGIN TRANSACTION; | ||
| Line 96: | Line 94: | ||
| COMMIT TRANSACTION; | COMMIT TRANSACTION; | ||
| - | </source> | + | </code> |
| ==== Inserting New Records ==== | ==== Inserting New Records ==== | ||
| - | <source lang="sql"> | + | <code sql> |
| -- We need to use an insert parameter here so we can add the geometry well known binary | -- We need to use an insert parameter here so we can add the geometry well known binary | ||
| -- We also wrap this in a transaction so we can keep the main table and spatial index in sync | -- We also wrap this in a transaction so we can keep the main table and spatial index in sync | ||
| Line 108: | Line 106: | ||
| COMMIT TRANSACTION; | COMMIT TRANSACTION; | ||
| - | </source> | + | </code> |
| ==== Editing Records ==== | ==== Editing Records ==== | ||
| - | <source lang="sql"> | + | <code sql> |
| -- Here we update both tables in case the geometry changed. Also note that we are using a query parameter so we can pass in the geometry as well known binary | -- Here we update both tables in case the geometry changed. Also note that we are using a query parameter so we can pass in the geometry as well known binary | ||
| BEGIN TRANSACTION; | BEGIN TRANSACTION; | ||
| Line 119: | Line 117: | ||
| COMMIT TRANSACTION; | COMMIT TRANSACTION; | ||
| - | </source> | + | </code> |
| ==== Deleting Records ==== | ==== Deleting Records ==== | ||
| - | <source lang="sql"> | + | <code sql> |
| -- Here we delete the record from the main table and index. Note that we do this in a transaction to keep them in sync. | -- Here we delete the record from the main table and index. Note that we do this in a transaction to keep them in sync. | ||
| BEGIN TRANSACTION; | BEGIN TRANSACTION; | ||
| Line 130: | Line 128: | ||
| COMMIT TRANSACTION; | COMMIT TRANSACTION; | ||
| - | </source> | + | </code> |
| ==== Query a Table Based on an Extent ==== | ==== Query a Table Based on an Extent ==== | ||
| - | <source lang="sql"> | + | <code sql> |
| -- Here we select all the record in the bounding box provided. | -- Here we select all the record in the bounding box provided. | ||
| SELECT "id", "geometry", "name" FROM "austin_streets" WHERE "austin_streets"."id" IN | SELECT "id", "geometry", "name" FROM "austin_streets" WHERE "austin_streets"."id" IN | ||
| (SELECT "id" FROM "idx_austin_streets_geometry" WHERE "minx" < -97.7340560913086 AND "maxx" > -97.7399658203125 AND "miny" < 30.294225692749 AND "maxy" > 30.288595199585); | (SELECT "id" FROM "idx_austin_streets_geometry" WHERE "minx" < -97.7340560913086 AND "maxx" > -97.7399658203125 AND "miny" < 30.294225692749 AND "maxy" > 30.288595199585); | ||
| - | </source> | + | </code> |
| ===== SQLite Dependencies ===== | ===== SQLite Dependencies ===== | ||
| - | We use the unmanaged implementation of SQLite provided by the creators via a NuGet package. Specifically, we use the package ‘System.Data.SQLite Core (x86/x64)’ because it has no other dependencies and supports both 32 and 64 bit application. When you add a reference to our SQLiteExtension, it will require you to add a reference to this NuGet package from your main application so that the unmanaged assemblies are included in the bin. As they are copied to the bin we support x-copy deployment and avoid potential conflict with other instances of SQLite that may exist on the machine. The specific package we use is the {{Is there a missing word here?|error}} It is currently not suitable for a fully managed deployment. If you have such an application, please let us know and we can consider another approach for your specific project. | + | We use the unmanaged implementation of SQLite provided by the creators via a NuGet package. Specifically, we use the package ‘System.Data.SQLite Core (x86/x64)’ because it has no other dependencies and supports both 32 and 64 bit application. When you add a reference to our SQLiteExtension, it will require you to add a reference to this NuGet package from your main application so that the unmanaged assemblies are included in the bin. As they are copied to the bin we support x-copy deployment and avoid potential conflict with other instances of SQLite that may exist on the machine. The specific package we use is the [[Is there a missing word here?|Template:Is there a missing word here? ]] It is currently not suitable for a fully managed deployment. If you have such an application, please let us know and we can consider another approach for your specific project. |
| https://www.nuget.org/packages/System.Data.SQLite.Core/ | https://www.nuget.org/packages/System.Data.SQLite.Core/ | ||
map_suite_sqlite_guide.1440040124.txt.gz · Last modified: 2015/09/16 09:47 (external edit)