"not authorized" error when trying to load SpatiaLite in UWP c#

[louwho]

 I have been searching for an example of using SQLite\SpatiaLite, using c#.  I am trying to do this in UAW, but it appears that SpatiaLite is not yet available to UWP (will it ever be?).   I get an “not authorized” exception when I do the “cmd.ExecuteNonQuery();” in the code below.

 

When in UWP (or not), I cannot find an example of loading SpatiaLite in the C# code.  I would settle for a non UWP working example if I could find one (one with an example of using SpatiaLite to do something as simple as a polygon would be great.).  I am not so much interested right now in the writing and reading from the DB, as I am in using the spatial functions to complete a polygon etc.

 

If my code looks rough, it is because I do not code all the time in C#.

Whether UWP or not, do I need to match the version of the spatialite\mod file to the 3.12.2 as shown in the references section below.  Where does that Mod file need to be?  If building on x64 system needs to be x64 file? (the amd64 is confusing, is that just for AMD processors?)  Is there a way to get more specific error information from SQLite?

 

------------------------------------------------------------

using SQLite.Net;

using SQLite.Net.Attributes;

 

using System;

using System.Collections.Generic;

using System.Diagnostics;

using System.IO;

using System.Linq;

using System.Runtime.InteropServices.WindowsRuntime;

 

using Windows.Foundation;

using Windows.Foundation.Collections;

 

using Windows.UI.Xaml;

using Windows.UI.Xaml.Controls;

using Windows.UI.Xaml.Controls.Primitives;

using Windows.UI.Xaml.Data;

using Windows.UI.Xaml.Input;

using Windows.UI.Xaml.Media;

using Windows.UI.Xaml.Navigation;

 

namespace UWPSQLiteDemo

{

 

    public sealed partial class MainPage : Page

    {

        string path;

        SQLite.Net.SQLiteConnection conn;

 

        public MainPage()

        {

            this.InitializeComponent();

            path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,

               "LouSQlite.db");

            conn = new SQLite.Net.SQLiteConnection(new

               SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);

            conn.CreateTable<Customer>();

 

 

            try

            {

                conn.EnableLoadExtension(1);

            }

            catch (SQLiteException e)

            //catch (Exception e)

            {

                Debug.WriteLine("{0} enable  Exception caught.", e);

            }

 

 

            SQLiteCommand cmd = conn.CreateCommand($"SELECT load_extension('mod_spatialite.dll')");

            try

            {

                cmd.ExecuteNonQuery();

 

            }

            catch (SQLiteException e)

            {

                //https://www.sqlite.org/rescode.html - search for error code ex: "not authorized"

                //(23) SQLITE_AUTH

                //The SQLITE_AUTH error is returned when the authorizer callback indicates that an SQL statement being prepared is not authorized.

                Debug.WriteLine("{0} execute Exception caught.", e);

            }

 

        }

[Brad Hards]

> I have been searching for an example of using SQLite\SpatiaLite, using c#. I am trying to do this in UAW, but it appears that SpatiaLite is not yet available to UWP (will it ever be?). I get an “not authorized” > exception when I do the “cmd.ExecuteNonQuery();” in the code below.

I can't see a compiled extension ever working in "universal" environment. How could it possibly work?

> When in UWP (or not), I cannot find an example of loading SpatiaLite in the C# code. I would settle for a non UWP working example if I could find one (one with an example of using SpatiaLite to do ?

> something as simple as a polygon would be great.). I am not so much interested right now in the writing and reading from the DB, as I am in using the spatial functions to complete a polygon etc.

> SQLiteCommand cmd = conn.CreateCommand($"SELECT load_extension('mod_spatialite.dll')");

This is roughly the right idea (except you don't need the .dll part if you have a recent enough SQLite - see https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

The most likely problem is that you don't have extension loading support. That is likely because you're in the "tablet" API.

It will probably be a lot easier if you use System.Data.SQLite - https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

If you still have problems loading the extension, make sure that the dependencies for mod_spatialite are in your path.

Brad

[louwho]

As I mentioned, I am fairly new to C#, and SpatiaLite.  I do not understand everything that you said.

"That is likely because you're in the "tablet" API. - What does this mean, do I have the wrong Nuget packages?

System.Data.SQLite - that would definitely not be UWP?

" recent enough SQLite " - Do you mean that SpatiaLite is not needed, that these functions are now included in SQLite?

Is there a functioning example somewhere of doing these things?

[Brad Hards]

I do not believe there is any way you can load a compiled extension into a UWP application. “UWP” / WinRT is what I meant by the “tablet” API.

 

For System.Data.SQLite – please read the web page. It is not UWP.

 

For the “recent enough SQLite” – please read the linked web page in my original email. It explains about the API change.

 

If you want spatialite functions, then you still need spatialite. Spatialite is a C/C++ library and sqlite extension. If you insist on UWP, it is going to be very hard. I’d suggest making a desktop application first.

 

Brad

[louwho]

I am using System.Data.SQLite.  I have the contents of mod_spatialite-4.3.0a-win-amd64 in my bin\x64\debug (and release) folders.  My Env Path reflects this. I open my connection, I

EnableExtensions(true), I try multiple ways of loading the spatialite extension, and keep getting the error "The specified procedure could not be found".  

string p1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

string path = Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine) + ";" + p1;

Environment.SetEnvironmentVariable("Path", path, EnvironmentVariableTarget.Process);

var sqliteConnection = new SQLiteConnection(sqliteConnectionString);

sqliteConnection.Open();

sqliteConnection.EnableExtensions(true);

string mod_spatialite_dllPath = p1 + @"\mod_spatialite";

sqliteConnection.LoadExtension(mod_spatialite_dllPath);

//sqliteConnection.LoadExtension(mod_spatialite_dllPath, "sqlite3_extension_init");

//sqliteConnection.LoadExtension(interopPath, mod_spatialite_dllPath);

I have no idea of what I am missing.

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Duncan]

On Wednesday, May 11, 2016 at 11:51:34 PM UTC+8, louwho wrote:

I am using System.Data.SQLite.  I have the contents of mod_spatialite-4.3.0a-win-amd64 in my bin\x64\debug (and release) folders.  My Env Path reflects this. I open my connection, I

EnableExtensions(true), I try multiple ways of loading the spatialite extension, and keep getting the error "The specified procedure could not be found".  

I have no idea of what I am missing.

At the risk of stating the obvious, UWP targets multiple platforms, whereas your mod_spatialite and dependent libraries are targeting x64 only. 

This also is true for the unmanaged components of the mixed mode System.Data.SQLite (presumably it is x64).

There is a precompiled SQLite binary available for UWP, but so far it doesn't seem to be available for System.Data.SQLite. 

You could request it on the SQLite mailing list, but that would not get you over the hurdle of compiling mod_spatialite and dependent libraries for UWP

See How to: Use Existing C++ Code in a Universal Windows Platform App for more information. I imagine it would not be for the faint hearted, but would be very useful.

In a regular Windows app System.Data.SQLite should load extensions successfully and the simple way is to follow conn.EnableLoadExtension(true) with conn.LoadExtension('mod_spatialite')

hth

[Brad Hards]

> I am using System.Data.SQLite. I have the contents of mod_spatialite-4.3.0a-win-amd64 in my bin\x64\debug (and release) folders. My Env Path reflects this. I open my connection, I
> EnableExtensions(true), I try multiple ways of loading the spatialite extension, and keep getting the error "The specified procedure could not be found".

Have you tried investigating the problem? For example, have you tried doing it with a normal sqlite.exe command line script?

Do you have all the required dependencies (e.g. checking with depends or similar tool)?

Have you checked that your path really is what you expect?

Its quite difficult to say when you aren't describing your configuration.

Brad

[louwho]

I am not trying to do this in UWP right now.  I do know that there is SQLite for UWP, and it does work, but the SpatiaLite option is not available.

I found a C# example using SQLite, downloaded it, and attempted to add SpatiaLite to it.  I have put the entire solution in a 7Z file (https://drive.google.com/file/d/0B7aXRzJlXwuWTkl2dEd4eTZKM0U/view?usp=sharing)

I am a windows InstallShield Developer, I do not normally to C# development.  My system is Win 10 Pro\x64.  I am using VS 2015 Community, v 14.0.25123.00  Update 2.

Things that I had to do to get it working...

  After download, the solution did not work because it did not find System,Data.SQLite.  I added Nuget package System.Data.Sqlite v1.0.101 (which also added EF6, Ling, Core).  The build then worked.

  Added x64 configuration.  Tested, program ran fine, SQLite table created, no errors.

  Copied contents of Mod_Spatialite4.3.0a-win-amd64 to bin\x64\debug (and release).  (contents are in the zip file in one of those folders, tried to make smaller zip file)

  Added the Path code, test and used Text Visualizer, and saw that the path was added.

  Added the mod path code, and load code.

 Commented out the last line of code, that actually does the load of the extension.  Tested, and code works fine.

 Uncommented that last line, ran code, and (both debug and release), get messagebox  (Vshost.exe has stopped working)

 Went to properties\debug, unchecked 'enable VS hosting process'. 

 Ran code again, Get error "The specified procedure could not be found'

 

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Duncan]

            SQLiteCommand cmd = conn.CreateCommand($"SELECT load_extension('mod_spatialite.dll')");

Not sure about the $ prefix. If that is for a string literal, shouldn't it be @? 

You could also try removing the '.dll' which is not required. It may try to load mod_spatialite.dll.dll

I generally haven't had to specify paths if the dlls are in the same directory as the assembly 

conn.LoadExtension('mod_spatialite') should work OK too

[Pepijn Van Eeckhoudt]

$"" is for string interpolation. Not necessary in this case but shouldn't cause any problems either.

The not authorised error is most likely being caused by extension loading not being enabled. Relevant documentation is at https://www.sqlite.org/lang_corefunc.html#load_extension and https://www.sqlite.org/c3ref/enable_load_extension.html.

I haven't used SQLite.Net-PCL myself yet, but it looks like calling 'conn.EnableLoadExtension(1)' before executing the 'select load_extension...' query should do the trick.

Hope this helps,

Pepijn

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[louwho]

It is confusing to me that there appears to be many ways to try this.  I am doing extension loading in the sample that I posted.  I was pointed here to System.data.sqlite (it being non UWP).  There is a UWP version, but it does not do SpatiaLite.  The previous posting here mentions SQLite.net-pcl (Xamarin and Win-8). If I am not doing UWP, which one should I be using? Is there a working example (in any of these), anywhere?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[louwho]

A little bit more here.  One example uses a "select" statement, one does not.  One line mentions SQLite3, the others do not.  Very confusing.

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Pepijn Van Eeckhoudt]

Following up on my earlier reply, I had missed that you were already calling EnableLoadExtension(1) on your connection object, so disregard the comment stating that is necessary.

To load an extension there are two options. You either use the SQLite C function sqlite3_load_extension or you use the load_extension SQL function. The SQL function delegates to the C function so which one you use doesn't make much of difference. Using the C function directly is typically not possible or convenient when using managed wrappers.

The 'not authorized' error you're getting is raised in the SQL function and in the C function. I can't link to the relevant lines directly, but search for 'not authorized' in both and you'll see where this error is raised. In both cases this happens when extension loading has not yet been enabled on the SQLite database connection. Since you're doing that already in your code I don't know why the loading is failing.

Pepijn

[louwho]

I decided to try doing things differently.  Instead of the

"conn.LoadExtension(mod_spatialite_dllPath);", I instead did the "select"...

---------------------------------------------------------------------------------------

var conn = new SQLiteConnection("Data Source=" + filename + ";Version=3;");

 try 
 { 
 conn.Open(); 
 SQLiteCommand command = new SQLiteCommand(sqlDropTable, conn); 
 command.ExecuteNonQuery();

 //create new table 
 SQLiteCommand command2 = new SQLiteCommand(sqlCreateTable, conn); 
 command2.ExecuteNonQuery();

 conn.EnableExtensions(true); 
 // gets "not authorized" error 
 //conn.LoadExtension(mod_spatialite_dllPath); 
 // instead use the Select command 
 using (SQLiteCommand mycommand = new SQLiteCommand("SELECT load_extension(\"mod_spatialite\")", conn)) 
 { 
 mycommand.ExecuteNonQuery(); 
 }

-----------------------------------------------------------------------------------------------------

There was no error this time.

Not being all that familiar with this, could you add  something to the code above that would test that this is really working?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Brad Hards]

I would test it by getting the spatialite version.
"SELECT spatialite_version()"
And
ExecuteScalar()

[louwho]

I could not use the code that you gave me, had to modify, but still have problems...""input string was not in the correct format""

}

//test to see if load of Spatialite was successful

using (SQLiteCommand mycommand2 = new SQLiteCommand("SELECT spatialite_version();", conn))

{

//next line = red underline: cannot implicitly convert object to int, are you missing a cast?

//retVal = (mycommand2.ExecuteScalar();

// next line gets exception "input string was not in the correct format"

retVal = Convert.ToInt32(mycommand2.ExecuteScalar());

MessageBox.Show("SpatiaLite version: " + retVal.ToString());

}

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Brad Hards]

This is nothing to do with spatialite – its basic C# / SQLite code.

using (SQLiteCommand mycommand2 = new SQLiteCommand("SELECT spatialite_version()", conn))
{

String version = mycommand2.ExecuteScalar() as String;
}

[louwho]

I was trying to follow other version examples that I had found.  Your string version works.

SQLite version = 3.12.2

SpatiaLite version = 4.3.01

So, it appears that the load of SpatiaLite was successful.  Is it the correct version to be using with SQLite 3.12.2 ?

I see plenty of examples of creating tables.  Can anyone point me to examples of using C# and SpatiaLite to  create a polygon (either from a table, or hard coded values), and then showing it?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Noel Frankinet]

Hello,

Polygons, lines, points are stored in a special type column like other columns in tables.
To insert and extract information from theses special columns, use SQL with spatial operators.
To 'show' a geometry, its another story, you will have to extract geometry ponts and transmit it to your graphic library, in order to see something.

Best wishes

Noël

--

You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

--

Noël Frankinet
Strategis sprl
0478/90.92.54

[louwho]

I am wondering if I am in the right place....

I have a C# WPF solution that currently uses Microsoft.SqLServer.Types (Geometry). Currently, it does not store anything in the database, but I wish to change that (it is desktop now, I want to add mobile). I do not want to have to connect online to a database, I am looking to include the database with the app, and so I am looking at SQLite, and SpatiaLite.

I take some metes and bounds, draw a polygon, and calculate the acreage, and display it. I am hoping to replace the following SQLGeometry code with the equivalent in Spatialite…

Begining code lines (that needs to be converted from using SQLServer, to something else…

SqlGeometry geom = GetGeometry(segs);
area = geom.STArea().Value;
System.Drawing.Image img = GetImage(geom, "ft");

    private static SqlGeometry GetGeometry(SurveySegment[] segments)
    {
        SqlGeometryBuilder builder = new SqlGeometryBuilder();
        builder.SetSrid(0); // Don't need an SRID here
        builder.BeginGeometry(OpenGisGeometryType.Polygon);

        // Walk around the segments
        PointF p = new PointF(0, 0);
        builder.BeginFigure(p.X, p.Y);
        for (int i = 0; i < segments.Length; i++)
        {
            p += segments[i].FromOrigin;
            builder.AddLine(p.X, p.Y);
        }

        // Polygon's end point must be at the origin
        if (p.X != 0 || p.Y != 0)
            builder.AddLine(0, 0);

        builder.EndFigure();
        builder.EndGeometry();

        // Return Geometry
        return builder.ConstructedGeometry;
    }

PointF[] ps = new PointF[geom.STNumPoints().Value];
SqlGeometry temp = geom.STPointN(i + 1);
SqlGeometry centroid = geom.STCentroid();

End code…

Am I going to be able to do this using SpatiaLite (if so, can someone give me an example)?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Brad Hards]

There is no geometry builder class in spatialite. You can build points
(MakePoint() function), lines (MakeLine() function) and polygons
(MakePolygon() function) in SQL though - all of those are documented in the
list at http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html and some
are demonstrated in the cookbook
(http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html).

There are direct equivalent functions to get the area and centroid. I'll let
you read the documentation.

This has turned into "do my project for me one email at a time". No more,
you've used up your share of my time - you have to do the programming
yourself.

Brad

[louwho]

While I do admit to not being a full time OOP developer, no one asked you to code my entire program.  The code is many more lines than what I asked for help with.  I showed the lines that were the reason that I was hoping that SpatiaLite was the tool that I could use to replace the SQLServer code.  I admit to even less knowledge with Spatial\Geometry.  If you feel that you already spent too much time with me, then perhaps someone should better document how to get SpatiaLite set up, it appears that many others had and have the same problems.  A couple of small Visual Studio solutions (showing different ways of getting set up), that include all the Nuget packages, and current documentation as to what to pull down from where, would probably have you spending less time on these matters.

 

From the beginning I should have just shown those lines and asked if what they were doing could be done in SpatiaLite.  Your last response (to someone who is new to these things), is cryptic to me.  You say that SpatiaLite does not have a geometry builder class, and you point me to SQL. Without doing all the code that I showed, a “here’s how you would start” example would have been more helpful, then “No more, you've used up your share of my time - you have to do the programming yourself. “

 

Should I be looking at another tool other than SpatiaLite? If this is not a tool that can be used in UWP eventually (no one is maintaining this?), then it will be a dead end for future development.  Can anyone else help me out here?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Noel Frankinet]

Interface to spatial type is done thru sql. You can use WKT format to pass complete geometry to and from spatialite.

What is exactly your problem ?

Noël

--

You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[mj10777]

On Monday, 23 May 2016 12:47:29 UTC+2, louwho wrote:

While I do admit to not being a full time OOP developer, no one asked you to code my entire program.  The code is many more lines than what I asked for help with.  I showed the lines that were the reason that I was hoping that SpatiaLite was the tool that I could use to replace the SQLServer code.  I admit to even less knowledge with Spatial\Geometry.  If you feel that you already spent too much time with me, then perhaps someone should better document how to get SpatiaLite set up, it appears that many others had and have the same problems.  A couple of small Visual Studio solutions (showing different ways of getting set up), that include all the Nuget packages, and current documentation as to what to pull down from where, would probably have you spending less time on these matters.

My impression here is that you are getting the priorities wrong

- it is not the major task of a client software 

-- that is being used on multiple (ever often evolving) systems 

--- each of which have tools, that themselves are ever changing

to document how the tools needed, to get the client software, work

This is specially true of the Windows developments systems

- which changes regularly

-- and make existing samples often swiftly obsolete

It is the main task/responsibility of the developer

- to learn how the tools they need are used

- and what capability they have

In your scenario, you are using a rather newer system

- where a wide spread experience does not exist

It is for you to find out if the 'sqlite' driver used by your c# NET system

- supports external extensions

As you have noted, this is a common problem.

Often the supplied 'sqlite' version, for security reasons, does not offer external extension support

- the 'howto' to to replace the 'sqlite' version being used by the c# NET system

-- is something that, most reliably, can be answered in the appropriate c# NET system for UWP

--- not here, since there seems to be nobody using UWP

Here the attempt was made, with a correct practical sample ('SELECT spatialite_version()')

- to test whether the pre-conditions exist for you to use spatialite (which is an external extension) on your system

Once these pre-conditions are fulfilled

- i.e. 'SELECT spatialite_version()' returns a valid result

-- thus spatialite is active and assistance can be give (being an area which were are more knowledgeable about) on how to complete your specific tasks 

 

From the beginning I should have just shown those lines and asked if what they were doing could be done in SpatiaLite.  Your last response (to someone who is new to these things), is cryptic to me. 

 The learning curve is always high on these things

- how to get:

-- the compiler working

-- getting the dependencies working

Based on past experience

- assistance can be (and was) given on how to check whether the needed pre-conditions are fulfilled

But it is up to you, based on your specific environment (UWP, Net version)

- to get this working

You say that SpatiaLite does not have a geometry builder class, and you point me to SQL.

His answer was correct

- as a c# programmer you should already know that the SqlGeometryBuilder() is part of the NET API

Without doing all the code that I showed, a “here’s how you would start” example would have been more helpful,

He gave multiple samples on how to use spatialite SQL 

- look at:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

at the wide range pf possibilities, such as:

SELECT ST_AsBinary(GeomFromEWKT('SRID=-1;POLYGON((0 492,512 492,512 1004,0 1004,0 492))'));

Based on your sample:

- you would build the POLYGON String from your POINTS

-- GeomFromEWKT will create a Spatialite-specific Geometry from that string

-- ST_AsBinary: will return the Spatialite-specific Geometry to the 'Well-known Binary representation'

--> check your NET classes how to convert the returned result to what you need, possibly:

SqlGeometry.Read Method

Reads a binary representation of a geometry type into a SqlGeometry object.

>> then “No more, you've used up your share of my time - you have to do the programming yourself. “

Granted, that this could have been stated more gracefully

- but does not change the fact that it is essentially true

 

Should I be looking at another tool other than SpatiaLite?

For stand alone solutions: most probably not

Please remember, that GIS-API's such as PostGIS and Spatialite

- are the last stage of a combination (a bringing together) of specialized knowledge 

-- proj4:   Transformations (ST_Transform)

-- GEOS: the nitty, gritty deep down GIS-tasks

with the main goal being: not to reinvent the wheel again.

PostGIS: for use in servers

 (with POSTGRES being the background Database-System)

Spatialite: in standalone environments

 (with sqlite being the background Database-System)

- dealing (with their API), each for their specific needs, in getting the GIS-Tasks task done as simply as possible

-- mainly through the use of SQL-Statements

This is the main development being done here.

Assistance is given in getting this up and going

- how to get compiled on known systems (of which there to many for a small team to deal with)

-- often User experiences are incorporated into the documentation   

If this is not a tool that can be used in UWP eventually (no one is maintaining this?), then it will be a dead end for future development.

It would not be the first time that a Microsoft product, which does not permit (or makes very difficult) the inclusion of external products

- to fade away

  Can anyone else help me out here?

Hope this helps.

And if you get this working

- a summery on how to deal with UWP-Specific aspects would be useful

Mark

[louwho]

I am not an OOP developer.  I have little spatial knowledge.  I have looked for examples, I have looked at what I was pointed to, I cannot figure it out.  Everything seems to be designed along the lines of writing to or extracting from a DB in c#.  For the time being I just want to create a polygon (without using SQL Server and its geometry functions). 

Just help get me started. In C#, using spatialite, (Not selecting from a db, (no SQL statements needed), using hard coded values), using points, draw a polygon, get its area.  This would give me examples of the specific Sparialite syntax that I need to be looking at.

This cannot be a lot of lines of code.

Is there anyone out there that believes that you do not always have to take the hard knocks (as long as you are not asking for the world)?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

[Jukka Rahkonen]

Hi,

I believe that developers here are not especially familiar with C#, UAW,
UWP etc. Dotspatial project seems to have support for Spatialite,
perhaps you can find help from there http://dotspatial.codeplex.com/ or
from the MapWindow project.

-Jukka Rahkonen-

>> //https://www.sqlite.org/rescode.html [1] - search

>> for error code ex: "not authorized"
>>
>> //(23) SQLITE_AUTH
>>
>> //The SQLITE_AUTH error is returned when the
>> authorizer callback indicates that an SQL statement being prepared
>> is not authorized.
>>
>> Debug.WriteLine("{0} execute Exception caught.", e);
>>
>>
>> }
>>
>> }
>

> --
> You received this message because you are subscribed to the Google
> Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to
> spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.
>
>

> Links:
> ------
> [1] https://www.sqlite.org/rescode.html

[louwho]

I am grateful for the last reply, but I already have the SQLite connection and  SpatiaLite load.  I am trying to (without accessing the DB), use spatialite functions to create a polygon and get its area (without using SQL server).  How would I do this using spatialite?  Thanks.

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

 I have been searching for an example of using SQLite\SpatiaLite, using c#.  I am trying to do this in UAW, but it appears that SpatiaLite is not yet available to UWP (will it ever be?).   I get an “not authorized” exception when I do the “cmd.ExecuteNonQuery();” in the code below.

 

When in UWP (or not), I cannot find an example of loading SpatiaLite in the C# code.  I would settle for a non UWP working example if I could find one (one with an example of using SpatiaLite to do something as simple as a polygon would be great.).  I am not so much interested right now in the writing and reading from the DB, as I am in using the spatial functions to complete a polygon etc.

 

If my code looks rough, it is because I do not code all the time in C#.

Whether UWP or not, do I need to match the version of the spatialite\mod file to the 3.12.2 as shown in the references section below.  Where does that Mod file need to be?  If building on x64 system needs to be x64 file? (the amd64 is confusing, is that just for AMD processors?)  Is there a way to get more specific error information from SQLite?

 

                //https://www.sqlite.org/rescode.html - search for error code ex: "not authorized"

[Jukka Rahkonen]

Hi,

Sorry, I am mainly an end users, even I have had success with executing
SQL with Spatialite functions from Java.

I suppose that you want to execute something like
select ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1 1,1 0,0 0))'));

I do not understand how you could do it without accessing the DB, but I
see that before executing non-query you have made a connection so you do
access the db.

You wrote first "I cannot find an example of loading SpatiaLite in the
C# code" and now "I already have the SQLite
connection and SpatiaLite load". If you have SpatiaLite loaded for what
would you need another example? Could you consider to have a look to
some DotWindows/MapWindow examples on desktop and try to make the C#
code to work first before continuing into UAW of UWP which I do not know
anything about but which feel like they could bring some specific
troubles with them?

AMD64 in a variant of x86-64 architecture
https://en.wikipedia.org/wiki/List_of_CPU_architectures and it is not
only used in AMD processors. Running SpatiaLite on 32-bit Java requires
32-bit versions of mod spatialite and all dependencies and 64-bit java
requires everything to be 64 bit. I do not know how C# behaves but
having a mixture of 32/64 does not feel good.

-Jukka Rahkonen-

>> //https://www.sqlite.org/rescode.html [1] - search

>> for error code ex: "not authorized"
>>
>> //(23) SQLITE_AUTH
>>
>> //The SQLITE_AUTH error is returned when the
>> authorizer callback indicates that an SQL statement being prepared
>> is not authorized.
>>
>> Debug.WriteLine("{0} execute Exception caught.", e);
>>
>>
>> }
>>
>> }
>

[Brad Hards]

using System;
using System.Data.SQLite;

namespace spatialiteCsharp
{
class Program
{
static void Main(string[] args)
{
SQLiteConnection conn = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
conn.Open();
conn.EnableExtensions(true);
conn.LoadExtension("mod_spatialite");
SQLiteCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT spatialite_version()";
String version = cmd.ExecuteScalar() as String;
Console.Out.WriteLine("SpatiaLite Version: " + version);

cmd.CommandText = "SELECT ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1 1,1 0,0 0))'))";
double area = (double)cmd.ExecuteScalar();
Console.Out.WriteLine("Area of 1 x 1 square: {0}", area);

Console.Out.WriteLine("Press any key to close test application");
Console.ReadKey();
}
}
}


Temporarily (for a few days), the whole solution for VS2013 is available at
https://drive.google.com/file/d/0BzouoZCWtrv8aUxjREl6R0xGQTA/view?usp=sharing

Brad

[louwho]

Here is where my confusion\ignorance\newness to all this comes into play (this, and the last reply)....

cmd.CommandText = "SELECT ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1 1,1 0,0 0))'))"; 
double area = (double)cmd.ExecuteScalar();

Are you telling me here that I MUST pull from a DB in order to do what I want?  That I cannot get the user input, use Spatialite functions to calculate a polygon (draw it for display also), and calculate the area?

OR

As I look at the code above, I do not see a table specified (to indicate where to get that Polygon data from).  Should I infer that this is how Spatialite functions are called\executed, in that you must execute a Spatialite cmd (passing the polygon points to it)...unlike the following SQL Server code

"SqlGeometryBuilder builder = new SqlGeometryBuilder();", where the DB is not being accessed?

On Wednesday, May 4, 2016 at 12:48:47 PM UTC-4, louwho wrote:

 I have been searching for an example of using SQLite\SpatiaLite, using c#.  I am trying to do this in UAW, but it appears that SpatiaLite is not yet available to UWP (will it ever be?).   I get an “not authorized” exception when I do the “cmd.ExecuteNonQuery();” in the code below.

 

When in UWP (or not), I cannot find an example of loading SpatiaLite in the C# code.  I would settle for a non UWP working example if I could find one (one with an example of using SpatiaLite to do something as simple as a polygon would be great.).  I am not so much interested right now in the writing and reading from the DB, as I am in using the spatial functions to complete a polygon etc.

 

If my code looks rough, it is because I do not code all the time in C#.

Whether UWP or not, do I need to match the version of the spatialite\mod file to the 3.12.2 as shown in the references section below.  Where does that Mod file need to be?  If building on x64 system needs to be x64 file? (the amd64 is confusing, is that just for AMD processors?)  Is there a way to get more specific error information from SQLite?

 

                //https://www.sqlite.org/rescode.html - search for error code ex: "not authorized"

[Mark Johnson]

2016-06-07 13:01 GMT+02:00 louwho <louels...@gmail.com>:

Here is where my confusion\ignorance\newness to all this comes into play (this, and the last reply)....

cmd.CommandText = "SELECT ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1 1,1 0,0 0))'))"; 
double area = (double)cmd.ExecuteScalar();

Are you telling me here that I MUST pull from a DB in order to do what I want?  That I cannot get the user input, use Spatialite functions to calculate a polygon (draw it for display also), and calculate the area?

OR

As I look at the code above, I do not see a table specified (to indicate where to get that Polygon data from).  Should I infer that this is how Spatialite functions are called\executed, in that you must execute a Spatialite cmd (passing the polygon points to it)...unlike the following SQL Server code

Look at the given code more closely

-  'Data Source=:memory'

-- look at the sqlite3 documentation for what this is (hint: it is not a file)

--

You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/yEEC-gbUHOc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.

[mj10777]

On Tuesday, 7 June 2016 12:39:09 UTC+2, Brad Hards wrote:

using System;
using System.Data.SQLite;

namespace spatialiteCsharp
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
            conn.Open();
            conn.EnableExtensions(true);
            conn.LoadExtension("mod_spatialite");
            SQLiteCommand cmd = conn.CreateCommand();

Adding  the following would be needed when using

- any geometries with srid or with spatialfuncions that need a srid:

cmd.CommandText = "SELECT InitSpatialMetadata(1)";

Mark

[Jukka Rahkonen]

louwho wrote 2016-06-07 14:01:
> Here is where my confusion\ignorance\newness to all this comes into
> play (this, and the last reply)....
>
> cmd.CommandText = "SELECT ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1
> 1,1 0,0 0))'))";
> double area = (double)cmd.ExecuteScalar();
>
> Are you telling me here that I MUST pull from a DB in order to do what
> I want? That I cannot get the user input, use Spatialite functions to
> calculate a polygon (draw it for display also), and calculate the
> area?

If you look at the SpatiaLite function ST_Area in
https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html
it is defined as
ST_Area( s Surface ) : Double precision
So it reads a geometry of type "Surface" (polygon) and it returns area
as double.
It is not defined where the input surface comes. It can come from a
database as in
SELECT ST_Area(geometry) from my_polygon_table;

However, also another SQL function may create a suitable geometry. In
this example ST_GeomFromText is used and syntax is:
GeomFromText( wkt String [ , SRID Integer] ) : Geometry

You can just fine take your user input, convert it into Well Knowt Text,
feed it into ST_GeomFromText and make Spatialite to construct a polygon
and compute the area of it. I would not say that you must pull anything
from the database. You do not see anything like "SELECT something FROM
some_table..." You don not need to have any tables or data in the
database but you must have an open connection to the database engine
before you can run the functions.

> OR
> As I look at the code above, I do not see a table specified (to
> indicate where to get that Polygon data from). Should I infer that
> this is how Spatialite functions are called\executed, in that you must
> execute a Spatialite cmd (passing the polygon points to it)...unlike
> the following SQL Server code

You see right, there are no tables involved. You select an output from a
function by giving the compulsory input parameters.

> "SqlGeometryBuilder builder = new SqlGeometryBuilder();", where the DB
> is not being accessed?

It is exactly the same with SQLite/Spatialite. You access a function
that is processed by the database engine. The syntax is slightly
different but don't pay too much attention in seeing "SELECT" there.
Imagine that it means "START_FUNCTION(insert parameters with right
syntax)".


-Jukka Rahkonen-

[louwho]

I have successfully tested getting the area of the Polygon (thanks).  Now I am trying to understand what is happening (yes, I am dense at times).

 

In the old code there is… “SqlGeometry geom = GetGeometry(segs);”.  Here you are instantiating a SqlGeometry.  Basically, it now exists, and I can use it to do what I need, I can refer to it at any time.

 

In the Spatialite world, on this page (https://www.gaia-gis.it/spatialite-2.1/SpatiaLite-manual.html#t4.2wkt), it says “Geometry (non-instantiable)”, “Polygon (instantiable)”.  Ok, I cannot instantiate the Geometry (previously pointed out to me), but it says that I can instantiate the Polygon.

 

When I do the “cmd.CommandText = "SELECT ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1
> 1,1 0,0 0))'))";” and then execute it, is that Polygon instantiated and available to me from that point on, or do I have to keep recreating it to refer to it?

 

If I do something like…

       cmd.CommandText = "SELECT(PolyFromText(((0 0,0 1,1 1,1 0,0 0))'))";                

       var P1 = cmdPolygon.ExecuteScalar();

 

Is that Polygon (P1), now available to me to use?

 

I want to draw an image of that Polygon (display it on the screen).  In the old code, I would pass the instantiated geom to the code that does the drawing.

[mj10777]

On Sunday, 12 June 2016 17:40:51 UTC+2, louwho wrote:

I have successfully tested getting the area of the Polygon (thanks).  Now I am trying to understand what is happening (yes, I am dense at times).

 

In the old code there is… “SqlGeometry geom = GetGeometry(segs);”.  Here you are instantiating a SqlGeometry.  Basically, it now exists, and I can use it to do what I need, I can refer to it at any time.

 

In the Spatialite world, on this page (https://www.gaia-gis.it/spatialite-2.1/SpatiaLite-manual.html#t4.2wkt), it says “Geometry (non-instantiable)”, “Polygon (instantiable)”.  Ok, I cannot instantiate the Geometry (previously pointed out to me), but it says that I can instantiate the Polygon.

Pleas use the present day documentation:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html 

- spatialite 2.1 is very, very, very, very, very old

-- the above link will always show the present day documentation (i.e. from the latest version)

 

When I do the “cmd.CommandText = "SELECT ST_Area(ST_GeomFromText('POLYGON ((0 0,0 1,1
> 1,1 0,0 0))'))";” and then execute it, is that Polygon instantiated and available to me from that point on, or do I have to keep recreating it to refer to it?

- no, this command will return the area (ST_Area) of the created Polygon. 

The code I sent you was this:

SELECT ST_AsBinary(GeomFromEWKT('SRID=-1;POLYGON((0 492,512 492,512 1004,0 1004,0 492))'));

Explained with the following:

- you would build the POLYGON String from your POINTS

-- GeomFromEWKT will create a Spatialite-specific Geometry from that string

-- ST_AsBinary: will return the Spatialite-specific Geometry to the 'Well-known Binary representation'

--> check your NET classes how to convert the returned result to what you need, possibly:

 

If I do something like…

       cmd.CommandText = "SELECT(PolyFromText(((0 0,0 1,1 1,1 0,0 0))'))";    

- this is not a valid command, use the above sample 

            

       var P1 = cmdPolygon.ExecuteScalar();

- with the above (my sample) : the 'binary representation' will be in PI

 

Is that Polygon (P1), now available to me to use?

 

I want to draw an image of that Polygon (display it on the screen).  In the old code, I would pass the instantiated geom to the code that does the drawing.

which class do you use (the passing) for this?

-  'SqlGeometry' ?

-- if yes, look at this documentation:

SqlGeometry.Read Method

Reads a binary representation of a geometry type into a SqlGeometry object.

- the 'binary representation' mentioned there is what 'SELECT ST_AsBinary(...) returns (P1) 

Mark

[louwho]

It is funny (or not), the turns and twists of life.

In the last reply there was this...

-- ST_AsBinary: will return the Spatialite-specific Geometry to the 'Well-known Binary representation'

--> check your NET classes how to convert the returned result to what you need, possibly:

I said to myself..."What NET classes", and eventually came across DbGeometry.  What I was trying to do in SQLite\SpatiaLite, create a polygon, get its size, etc., I could do without using SQLite\SpatiaLite...

  DbGeometry polygonGeom = DbGeometry.FromText(strPolygon);

  double area2 = polygonGeom.Area.Value;

I cannot find anything for being able to do this in UWP (there are not yet any Spatial classes in UWP).  EF7 (renamed to "Entity Framework Core"), might have spatial someday.  It is on the list of things to do, but there is no timetable for it.