Using POLYGON in views
23 March, 2015
Hi,
In the SQL Server 2012 database I have stored polygons for Dutch postal codes. These objects can be very large, this is why they are stored as object type varchar(max). The largest one contains 5.655.044 characters.
When I want to use these objects in a chart things go wrong.
Below I copied the generated SQL sytax:
SELECT "GEO_GEMEENTE"."GEO",
SUM("GEO_STATISTICS"."AANT_INW_TOT")
FROM "dbo"."GEO_GEMEENTE" INNER JOIN "dbo"."GEO_STATISTICS" ON
( "GEO_GEMEENTE"."GEMEENTE_ID" = "GEO_STATISTICS"."GEMEENTE_ID" )
GROUP BY "GEO_GEMEENTE"."GEO"
The returned error for this SQL is:
[color="#FF0000">
Msg 306, Level 16, State 2, Line 10
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
[/color]
Is there a qork around for this error in YF or should the way the polygon is stored be changed in database side?
Regards,
Gert
In the SQL Server 2012 database I have stored polygons for Dutch postal codes. These objects can be very large, this is why they are stored as object type varchar(max). The largest one contains 5.655.044 characters.
When I want to use these objects in a chart things go wrong.
Below I copied the generated SQL sytax:
SELECT "GEO_GEMEENTE"."GEO",
SUM("GEO_STATISTICS"."AANT_INW_TOT")
FROM "dbo"."GEO_GEMEENTE" INNER JOIN "dbo"."GEO_STATISTICS" ON
( "GEO_GEMEENTE"."GEMEENTE_ID" = "GEO_STATISTICS"."GEMEENTE_ID" )
GROUP BY "GEO_GEMEENTE"."GEO"
The returned error for this SQL is:
[color="#FF0000">
Msg 306, Level 16, State 2, Line 10
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
[/color]
Is there a qork around for this error in YF or should the way the polygon is stored be changed in database side?
Regards,
Gert
Hi Gert,
sorry for the delay in responding, it seems that somehow this forum post "slipped through the cracks"!
To store a polygon as a CLOB is quite OK, as some of them can get very large indeed, thus you won't have to change the way the polygon is stored in the database. Instead, the trick is to convert it via the WKT Geometry Converter, you can find out more about this topic on this wiki page.
I hope that helps with your Dutch postal codes view, if there are any further questions then please let us know.
regards,
Dave
sorry for the delay in responding, it seems that somehow this forum post "slipped through the cracks"!
To store a polygon as a CLOB is quite OK, as some of them can get very large indeed, thus you won't have to change the way the polygon is stored in the database. Instead, the trick is to convert it via the WKT Geometry Converter, you can find out more about this topic on this wiki page.
I hope that helps with your Dutch postal codes view, if there are any further questions then please let us know.
regards,
Dave