View with Spatial Query explodes dotconnect

View with Spatial Query explodes dotconnect

Postby 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}
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Postby 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?
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Plain ADO.NET

Postby 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();
            };
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10

Postby 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).
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby 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.
drysg
 
Posts: 28
Joined: Fri 09 Mar 2012 15:10


Return to dotConnect for PostgreSQL