Select to view content in your preferred language

Problem with WKT in SQL Server 2008 R2 and SDE 10 SP1

3466
7
01-10-2011 07:44 AM
TiagoRibeiro
Occasional Contributor
Hi,

I'm inserting via SQL a bunch of multi-part lines, that are represented in WKT. I'm using the STIsValid() and MakeValid() functions of SQL Server.

All was well until I hit this part:
MULTILINESTRING((-97505.1929793411 -105143.43196521,-97505.1929775407 -105143.431963236),(-97529.3438960883 -105163.592387535,-97505.1929793411 -105143.43196521),(-97532.572884962 -105166.287443998,-97529.3438960883 -105163.592387535),(-97565.2298758472 -105184.926958411,-97532.572884962 -105166.287443998),(-97600.6628490141 -105206.56252583,-97565.2298758472 -105184.926958411),(-97621.1848213568 -105220.102861102,-97600.6628490141 -105206.56252583),(-97621.1848213568 -105220.102861102,-97691.0012017201 -105262.139530079))

For some reason (that I don't know why) when I add this line to the feature, ArcMap starts behaving strangely: sometimes it doesn't show the new line. If I delete this record from the table all goes to normal.

When I try to export to a shape using ArcMap, it gives the following error:
Error exporting data
The number of points is less than required for feature

This features has already more than 2000 records, and this is the only one giving me trouble.

To SQL Server it is valid, and it represents it in the spatial results tab.

I also tried to use PostGre/PostGis, it accepted the WKT geom and converted into GeoJson and I was able to represent that on QGis.

Can someone please help me understand whats wrong?

Thanks in advance,

Best regards,

Tiago
0 Kudos
7 Replies
VinceAngelo
Esri Esteemed Contributor
This is the same issue as a recent thread in this forum.

What it comes down to is the coordinate resolution used to describe the shapes --
When the table containing an existing geometry column is registered with ArcSDE,
a precision is defined for the X/Y dimension (and Z and M dimensions, if appropriate).
ArcSDE uses this precision to convert coordinate vales to internal representation.
If the geometry cannot be represented, then an error is raised on the query stream,
using the first transfer buffer available when the error was encountered (if the
error occurred on feature 100, it's still possible that only 70 shapes could have
been returned at the time the draw request failed).

If you use the ArcGIS Desktop standard of ~1mm precision, then you'd need to lop
seven places off the WKT strings (assuming the coordinate system unit is meters) to
evaluate them for possible conflict within ArcSDE's internal SE_SHAPE representation
(even still, other implementations aren't guaranteed to handle shapes identically).
If the layer was defined without an explicit coordinate reference, it may have used
the ArcSDE default coordref of "0,0,1" ({0,0} origin, 1 meter/foot/degree precision),
which should generally be avoided (especially when working with geographic coordinate
systems).  You can determine the precision registered with ArcSDE by using the command
'sdelayer -o describe_long -l {table},{geomcol} -u ...' (look for "Offset" and "System
Units").

If you want to preserve the (assumed) 10 nanometer precision in the WKT strings, then
the layer registration command would need to be altered to match that precision (using
the third parameter of the '-x' switch), with a scale value of 100000000 (multiplicitive
inverse of 0.000000001) -- though this may impact the range of coordinates accepted
within geometries (you only have 53 bits of precision available).

Please see the Understanding Coordinate Management in the Geodatabase white paper
for more information on how coordinate systems are constructed and managed within
ArcSDE.

- V
0 Kudos
VinceAngelo
Esri Esteemed Contributor
The part that is likely causing the issue is the first segment, which has a length [assuming meters]
of 2.67 microns (smaller than the radius of a red blood cell [as per Wikipedia]).

I was able to parse this WKT with an XY coordref of -200000,-200000,100000 (10 micron precision),
because the first vertex rounds north to {-97505.19298,-105143.43197} while the second vertex
rounds south to {-97505.19298, -105143.43196}.

- V
0 Kudos
TiagoRibeiro
Occasional Contributor
Thanks a lot Vangelo. I hadn't noticed that difference in the first segment.
I registered the table using a projection with a finer precision as you told me, and now everything is working fine. I didn't use the "-x", instead created a template feature class with the spatial reference and precision I wanted, and then added the new SRID to the "-R" parameter.
Thanks a lot for your help.

Tiago
0 Kudos
ValentyGonzalez
New Contributor

Hi Tiago, Angelo.

I have the same problem that you have resolved. I have tried with your instruction, but error persist.

I have an SQLServer2008RS SpatialDabatase and i want to migrate to a FileGeoDatabase using ArcGIS-Desktop-10.3, but i get: "Error exporting data; The number of points is less than required for feature".

I have tried: feature-to-feature; export xlm, export gml, enable enterprise geodatabase, create feature-class with more precision and nothing work.

Do you have some new idea?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

You should post a new question here in Managing Data, providing key details, and including the exact data which is failing.  Some of the things you have tried don't seem to have any bearing on the problem ("Enable Enterprise Geodatabase"?!), so you'll probably want to explain how each was relevant.

- V

0 Kudos
ValentyGonzalez
New Contributor

Thanks Angelo, i will post a new question.

(enable geodatabse because original database its sqlserver native with spatial data but is not a SDE database)

Regards,

0 Kudos
VinceAngelo
Esri Esteemed Contributor

Enterprise-enabling a SQL Server instance is not going to change how the RDBMS implements a native datatype, nor will it change how ArcGIS interacts with an unregistered table. You need to identify which row is causing the issue then review the exact geometry to determine what is wrong.  Using the native "IsValid" or "MakeValid" functions may be of help, but Microsoft has a different definition of Valid than Esri.

- V

0 Kudos