map_suite_sqlite
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 [2015/08/31 07:54] admin |
map_suite_sqlite [2015/09/28 03:27] (current) admin [SQLite Dependencies] |
||
|---|---|---|---|
| Line 24: | Line 24: | ||
| ===== SQLite APIs in Map Suite ===== | ===== SQLite APIs in Map Suite ===== | ||
| ==== Create a New Database ==== | ==== Create a New Database ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // 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"); | ||
| Line 30: | Line 30: | ||
| ==== Create a New Table ==== | ==== Create a New Table ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // 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. | ||
| + | SqliteFeatureSource.CreateTable(@"Data Source = C:\sampledata\austin_streets.sqlite; Version = 3;","sample_table_name",new SqliteColumn[] { new SqliteColumn("name", SqliteColumnType.Text)},GeographyUnit.DecimalDegree); | ||
| + | </code> | ||
| + | ==== Create a New View ==== | ||
| + | <code csharp> | ||
| + | // We create the view based on the View SQL passed in. We will create the view as well as build the spatial index based on the record in the view. | ||
| + | SqliteFeatureSource.CreateView(@"Data Source = C:\sampledata\austin_streets.sqlite; Version = 3;", "MajorRoads", "CREATE VIEW MajorRoads AS SELECT * austin_streets where road_type='major';", "id", GeographyUnit.DecimalDegree); | ||
| </code> | </code> | ||
| - | |||
| ==== Opening a Database ==== | ==== Opening a Database ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // 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. | ||
| // The same code is used to open the SqliteFeatureLayer. | // The same code is used to open the SqliteFeatureLayer. | ||
| Line 43: | Line 48: | ||
| ==== Inserting New Records ==== | ==== Inserting New Records ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // 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(); | ||
| Line 52: | Line 57: | ||
| ==== Editing Records ==== | ==== Editing Records ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // 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(); | ||
| Line 60: | Line 65: | ||
| ==== Deleting Records ==== | ==== Deleting Records ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // Here we are deleting features 1 and 2. | // Here we are deleting features 1 and 2. | ||
| sqliteFeatureSource.BeginTransaction(); | sqliteFeatureSource.BeginTransaction(); | ||
| Line 69: | Line 74: | ||
| ==== Opening a SQLiteFeatureLayer For Display or Analysis ==== | ==== Opening a SQLiteFeatureLayer For Display or Analysis ==== | ||
| - | <code lang="csharp"> | + | <code csharp> |
| // 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"); | ||
| Line 79: | Line 84: | ||
| ==== Create a New Table ==== | ==== Create a New Table ==== | ||
| - | <code 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 98: | Line 103: | ||
| ==== Inserting New Records ==== | ==== Inserting New Records ==== | ||
| - | <code 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 110: | Line 115: | ||
| ==== Editing Records ==== | ==== Editing Records ==== | ||
| - | <code 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 121: | Line 126: | ||
| ==== Deleting Records ==== | ==== Deleting Records ==== | ||
| - | <code 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 132: | Line 137: | ||
| ==== Query a Table Based on an Extent ==== | ==== Query a Table Based on an Extent ==== | ||
| - | <code 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 | ||
| Line 139: | Line 144: | ||
| ===== 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/ | ||
| ===== Management Tools ===== | ===== Management Tools ===== | ||
| - | If you want to explore the data outside of Map Suite you can use any number of open and closed source tools to examine SQLite. Below is a link to a list of the most popular tools. | + | If you want to explore the data outside of Map Suite you can use any number of open and closed source tools to examine SQLite. Below is a link to a list of the most popular tools. |
| - | + | \\ | |
| http://www.sqlite.org/cvstrac/wiki?p=ManagementTools | http://www.sqlite.org/cvstrac/wiki?p=ManagementTools | ||
| A nice portable option is Database Browser Portable at the link below. | A nice portable option is Database Browser Portable at the link below. | ||
| - | |||
| http://portableapps.com/apps/development/database_browser_portable | http://portableapps.com/apps/development/database_browser_portable | ||
map_suite_sqlite.1441007672.txt.gz · Last modified: 2015/08/31 07:54 by admin