map_suite_sqlite_guide
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
map_suite_sqlite_guide [2015/09/16 09:53] admin |
map_suite_sqlite_guide [2015/09/16 10:00] (current) admin [SQLite Dependencies] |
||
|---|---|---|---|
| Line 51: | Line 51: | ||
| ==== 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 78: | 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 94: | 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 106: | 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 117: | 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 128: | 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.1442397208.txt.gz · Last modified: 2015/09/16 09:53 by admin