Aspose.GIS sqlexception when reading from a table with spatial data

Aspose.GIS Product Family
1

Hello everybody,

I am trying to use Aspose.GIS to render a map with custom labels. I have several tables stored in a SQL Server Database that contain spatial data (along with other datatypes) that I am trying to access/read directly through Aspose.GIS. My code is as follows:

Aspose.Gis.License license = new Aspose.Gis.License();
license.SetLicense(“c:\download\Aspose.Total.lic”);

SqlConnection gisconn = new SqlConnection(“Data Source=gisserver;Initial Catalog=gis; Integrated Security=True”);
gisconn.Open();
using (var ds = Dataset.Open(gisconn, Drivers.SqlServer))
{
try
{
var magisterial_layer = ds.OpenLayer(“MAGISTERIAL_DISTRICTS_2011”);
using (var map = new Map(1600, 900))
{
string dataDir = “c:\download\”;
map.Add(magisterial_layer);
map.Render(dataDir + “testrender.png”, Renderers.Png);
}

                    }
                    catch (Exception ex)
                    {
                        string exc = ex.ToString();
                    }

I get a SQLException/ red x in the count when trying to use the OpenLayer method on any of the tables in the SQL Server Database. Another error I get is “invalid object name ‘table_name’” (where table_name is whatever table i’m trying to read from) and ((Aspose.Gis.Formats.Common.Sql.SqlReadLayer)magisterial_layer).Count = ‘((Aspose.Gis.Formats.Common.Sql.SqlReadLayer)magisterial_layer).Count’ threw an exception of type ‘System.Data.SqlClient.SqlException’. The tables all have multiple columns/datatypes like int, nvarchar, and geometry. What is the proper usage, or am I doing something unsupported by Aspose.GIS?

2

Hi, @taltal045

Thank you for your interest in the Aspose.GIS product.

Try to check the list of available tables (more detail in this example). There are reasons if a table is missed:

  • The table does not contain a geometry or geography column
  • The ‘.’ character in the table name can cause errors
  • Also we recommend using the default schema (dbo)

Please share sql table structure (the query to create table) and we will try to help you more.

Best Regards.

3

I followed the " Iterate over Layers in SqlServer database" example and it successfully retrieves the layer names from the database I am trying to read from, which reading form that example, should detect all tables from the database with a geometry column, which I confirmed exists in the table I’m trying to read. The structure is as follows:
[gis].[gis].[MAGISTERIAL_DISTRICTS_2011]

Creation query:

USE [gis]
GO

/****** Object: Table [gis].[MAGISTERIAL_DISTRICTS_2011] Script Date: 10/16/2020 2:03:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [gis].[MAGISTERIAL_DISTRICTS_2011](
[OBJECTID] [int] NOT NULL,
[MAG_DIST_ID] [int] NULL,
[MAG_DIST_CAMA_ID] nvarchar NULL,
[MAG_DIST_NAME] nvarchar NULL,
[GlobalID] [uniqueidentifier] NOT NULL,
[SHAPE] [geometry] NULL,
CONSTRAINT [R1739_pk] PRIMARY KEY CLUSTERED
(
[OBJECTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [gis].[MAGISTERIAL_DISTRICTS_2011] WITH CHECK ADD CONSTRAINT [g1622_ck] CHECK (([SHAPE].[STSrid]=(4)))
GO

ALTER TABLE [gis].[MAGISTERIAL_DISTRICTS_2011] CHECK CONSTRAINT [g1622_ck]
GO

4

Thank you for sharing the details. We’re investigate they and will get back after weekend .

5

Hi, @taltal045

We reproduced the issue in 20.10 version. And I have logged an the GISNET-532 ticket and update you here as soon as additional information is available.

Right now Aspose.GIS can’t read a table with non-default schema for SQL Server. As workaround, consider a possibility to create table in a schema which default for the user (it is usually ‘dbo’). Also it is possible to change the default user schema.

ALTER USER my_user WITH DEFAULT_SCHEMA = gis;

Please feel free to contact us if you have any other issues or difficulty using the product.

6

Thank you, looking forward to this being resolved.