View with Spatial Query explodes dotconnect

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
drysg
Posts: 28
Joined: Fri 09 Mar 2012 15:10

View with Spatial Query explodes dotconnect

Post by drysg » Tue 13 Mar 2012 20:24

I have a view that does a spatial intersection ST_Intersects on a PostGIS database.

The SQL view looks like this:

Code: Select all

CREATE OR REPLACE VIEW
    portal.viewboston AS
SELECT
    cat.idx,
    cat.product,
    cat.size_bytes,
    cat.date,
    cat.type,
    cat.classification,
    cat.distributor,
    cat.egpl_date,
    cat.classification_int,
    cat.handling,
    cat.creator,
    cat.datum,
    cat.elevation_ft,
    cat.description,
    cat.do_location,
    cat.bbox,
    cat.uniq_id::CHARACTER VARYING AS uniq_id
FROM
    portal.catalog cat
WHERE
    st_intersects(st_geomfromtext('POLYGON((-70 42,-71 42,-71 41,-70 41,-70 42))'::text, 4326),
    cat.location);
There was no problem using the entity framework to map this query. I then proceeded to write the following C#. The foreach blows up, but the real error happened on the line above it where I lazy eval'ed the query, (It was only when it tried to make it go concrete in the foreach that the error was recognized.

Code: Select all

        public CatalogEntry[] ViewBoston()
        {
            List values = new List();
            GPLCatalog context = new GPLCatalog();
            var query = from it in context.viewbostons
                        select it;
            foreach (viewboston item in query)




-

Code: Select all

		InnerException	{"function _st_intersects(public.geometry, public.geometry) does not exist"}	System.Exception {Devart.Data.PostgreSql.PgSqlException}
+		[Devart.Data.PostgreSql.PgSqlException]	{"function _st_intersects(public.geometry, public.geometry) does not exist"}	Devart.Data.PostgreSql.PgSqlException
+		Data	{System.Collections.ListDictionaryInternal}	System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
		HelpLink	null	string
+		InnerException	null	System.Exception
		Message	"function _st_intersects(public.geometry, public.geometry) does not exist"	string
		Source	"Devart.Data.PostgreSql"	string
		StackTrace	"   at Devart.Data.PostgreSql.s.e(af A_0)\r\n   at Devart.Data.PostgreSql.af.ah()\r\n   at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)\r\n   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)\r\n   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at Devart.Data.PostgreSql.Entity.h.a(CommandBehavior A_0)\r\n   at Devart.Common.Entity.aj.b(CommandBehavior A_0)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"	string
+		TargetSite	{Void e(Devart.Data.PostgreSql.af)}	System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 15 Mar 2012 12:58

Please try executing plain ADO.NET like:

Code: Select all

  PgSqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = "select * from portal.viewboston";
  PgSqlDataReader reader = cmd.ExecuteReader();
Does it work?

drysg
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Plain ADO.NET

Post by drysg » Thu 15 Mar 2012 20:45

This does work. So thank you for that.

But one of the reasons I bought the PRO version was your slick ORM tools, which would make the mapping a snap. This way it seems that I have to know which field is Int64, Int32, DateTime and String and the do the conversion myself.

Is that basically true? Or am I missing something.

Here is my current code (which works):

Code: Select all

            String str;
            Devart.Common.DbConnectionStringBuilder csb = new Devart.Common.DbConnectionStringBuilder();
            csb["Host"] = "localhost";
            csb["Port"] = 5432;
            csb["User Id"] = "postgres";
            csb["Password"] = "password"; // not the real password ;-)
            csb["Database"] = "GPL";
            csb["Max Pool Size"] = 150;
            csb["Connection Timeout"] = 30;
            PgSqlConnection conn = new PgSqlConnection(csb.ConnectionString);
            conn.Open();
            PgSqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from portal.viewboston";
            PgSqlDataReader reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    str = reader.GetInt64(0).ToString() +" " +   // DEVART: just and illustration for debug purposes... I have not finished this code
                        reader.GetString(1) + " " +
                        reader.GetString(2);
                }
            }
            finally
            {
                reader.Close();
                conn.Close();
            };

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 21 Mar 2012 13:22

We have answered you by e-mail (looking for the way how to call the search_box function via Entity Framework).

drysg
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Post by drysg » Wed 21 Mar 2012 13:53

Let's close this discussion thread -> Since we agree that views are not the way to get the data. But you probably should note in your documentation that PostGIS spatial functions do not work with views.

Post Reply