Subscribe to this thread
Home - General / All posts - Projection troubleshooting problem
bclement
273 post(s)
#01-Sep-22 17:08

I guess I am just a problem child. So here is the problem:

I have several layers downloaded from an ESRI REST endpoint via script and loaded into a SQL server. One of them is a parcel layer. It won't render in a drawing. By that, I mean that when I open the drawing, I just get a white rabbit in a snowstorm. Units are .000xxx by .000xxx degrees. I know I have read or seen how to get the actual data out of the geom before, but I can't seem to find it. SQL server knows how to render the dataset. I export the drawing out of V9 as a shapefile and making no changes whatsoever, import it into V8 and all is well. If I export out of V8 and import back into V9, all is well too. Obviously the data is there and well.

Can someone please remind me what/how to check in the geom field to find what wrong/missing projection information needs attention please? Oh, one more detail, fixing initial projection in V9 does not seem to make a difference. It is embarrassing for me to be having such a basic problem, but here I am.

oeaulong

427 post(s)
#01-Sep-22 21:10

I'd like to see a connection or some example of what it is.

what projection is the snowstorm layer again? Have you checked on the table schema?

bclement
273 post(s)
#02-Sep-22 00:17

oeaulong,

Interestingly enough, when I re-connected to the database in a new project, the change I made to the original projection stuck. It is EPSG 4326. Now that it knows that, it will center on reasonable looking coordinates, but still refuses to render. Before I repaired the initial coordinate system, I believe it was coming in 3163 but I cannot say for sure.

Not sure what you want me to check in the schema, but I will have to argue that it is fine since V8 shows it without making any change and V9 will display it once I run it through V8 without making changes. I will work on a test table.

adamw


10,175 post(s)
#02-Sep-22 07:23

From what you describe, it seems like we are asking a layer for its bounding box and it returns something that is way bigger than the actual extent of the data. Since we are talking about SQL Server, as a first step, you can quickly check the contents of 'sys.spatial_index_tessellations', it should contain the bounding box of the index.

Now, having a bounding box of the index be larger than the actual extent of the data is fairly normal. Because you might want to insert new data outside the extent of the current data. But zooming to this larger bounding box is not ideal. You can zoom to the selection (invoke Edit - Select All to select all geometry, then right-click the layer tab and invoke Zoom to Selection). Or you can add a base map (eg, Google Maps) and zoom to the desired region there.

bclement
273 post(s)
#02-Sep-22 15:41

Hmm, to quote a famous song, "One of these things is not like the other," (please see attached sys.spatial file)

Attachments:
SysDotSpatialIndex.csv

adamw


10,175 post(s)
#03-Sep-22 05:40

I take it that the index in the table that zooms weirdly has a rect of 0,0,1,1. It does stand out, indeed. Now, you said the data was created by downloading a layer from an ArcGIS REST server, then copying that to SQL Server. I don't have the data at hand, so I cannot verify that 0,0,1,1 is incorrect, but this does seem like some kind of a default value so I'll recheck what we do during copying to SQL Server. To clarify: was the index created automatically by 9 copying the table to SQL Server or was it added manually later? Also: selecting all records and zooming to selection still works, right?

bclement
273 post(s)
#06-Sep-22 17:11

So in reading your questions carefully, I see that I need to further clarify. The points are being pushed into the MS SQL database via python script as they are pulled from the REST endpoints. Several layers are being pulled in and the others are displaying fine. I am not sure what could be the difference as it is all geojson data but there could be an issue there.

Also, I was opening the drawing and then zooming to the extent and then ctrl + clicking and dragging to select all which did not work. I just tried the other way around which I was easily able to do thanks to the updated record handling (thanks ever so much) and it did not work either. Now by not working, I mean I still got a white rabbit in a snowstorm. But as mentioned, the units do look reasonable now.

So in trying several ideas again in a effort to further troubleshoot and sponsored by your questions, I realized that the export to .csv, re-import into V9 did not work as the geom column was not exported? Not sure why. It is almost as though the geom column is not being recognized which would sure explain why it won't draw! What is a reasonable next step?

Dimitri

7,090 post(s)
#07-Sep-22 06:13

the export to .csv, re-import into V9 did not work as the geom column was not exported?

CSV is a text format. It has no ability to store binary data like geoms. Everything that goes into a CSV must be text. It's like writing something into Notepad .txt - can't store images, videos, geoms, etc., in a Notepad .txt file.

You don't say why you're using CSV, but if you *must* use a text format to represent geographic data, well, you could use WKT (well-known text) format for geometry. Or, if it is points you're saving, you could keep them in text values that represent latitude / longitude values and then on the fly create geometry from those.

bclement
273 post(s)
#07-Sep-22 13:42

Wow, I knew this problem was going to be embarrassing, I just had no idea on how many levels! These things you are pointing out are so basic as to make one loose all credibility. I guess I am so used to Manifold working magic that I was expecting an automatic export to geojson which was what the data came from but there was no way for the software to know that. I certainly did not think that one through.

I was going to burn a tech support on this one because I am sure that the answer is simple and making fun of me even as we speak. I am just not sure how to send them my database . . .

Dimitri

7,090 post(s)
#07-Sep-22 14:14

I was expecting an automatic export to geojson

If you want to export a drawing, you can export that to geojson format if you like. Before spending a token for tech support, I'd keep discussing it here in the forum, maybe giving some more details on what you're doing. That can't hurt and it will help you refine things down if you do decide to call in tech support.

adamw


10,175 post(s)
#07-Sep-22 14:18

A small note: it is much better to use GEOJSONL instead of GEOJSON. GEOJSONL has no real limit on the size of read / written data, is significantly faster, etc. All because the format is friendlier.

Same for preferring JSONL over JSON.

bclement
273 post(s)
#07-Sep-22 16:27

Thanks. I was not even aware that there were such formats.

bclement
273 post(s)
#07-Sep-22 16:26

Yes, like I said, didn't think that one through. Or maybe I should just shorten it to didn't think. I'll keep trying here. Might as well own it now.

adamw


10,175 post(s)
#07-Sep-22 14:11

The points are being pushed into the MS SQL database via python script as they are pulled from the REST endpoints.

OK. But who created the table and specified the 0,0,1,1 bounds for the spatial index? If the table and the index were created by 9, I will check why we ended up with such bounds.

In general, you can drop and re-create the spatial index specifying tighter bounds and this should fix the zoom to fit. To compute the exact bounds, use something like (on SQL Server, you may connect to it in 9, open the command window for the SQL Server data source and run the query in that window):

--SQL

SELECT

  min(geom.STEnvelope().STPointN(1).STX),

  min(geom.STEnvelope().STPointN(1).STY),

  max(geom.STEnvelope().STPointN(3).STX),

  max(geom.STEnvelope().STPointN(3).STY)

FROM table;

...then increase the bounds somewhat to allow inserting new geometry, and use them when creating a new index:

--SQL

CREATE SPATIAL INDEX table_geom ON table(geom) WITH (

  BOUNDING_BOX = (0, 0, 100, 200)

);

The above assumes, however, that the reason you cannot see much when trying to zoom to fit is that the bounding box of the index is too big. There might be a second reason which I thought about after hearing that the table was populated by a script: most of the geometry lives someplace reasonable, but some of the geometry values refused to be read correctly or whatever and ended up with default coordinates far away from the real values. This happens sometimes: the real objects are, say, in the right top corner, and the fake objects that must have been NULLs but ended up with some default geometry because of the conversion process are in the opposite corner. So when you zoom to fit, you see nothing except two points - one in the right top and another in the left bottom. If this is the case with your data, you might see that from the bounds computed using STEnvelope, one pair of XY values will look weird, that's where the fake geometry might be.

Also, regarding selecting objects and zooming to the selection: I get that selecting using Ctrl-drag didn't result in much, but if you select everything by pressing Ctrl-A, and then zoom to the selection using the layer context menu, do you zoom to a reasonable view? If yes, we are in problem 1 (the data is fine, but the bounding box of the index is too big), otherwise we might be in problem 2 (some of the data is not fine, possibly due to geometry that should have been NULL converting to some default coordinates).

bclement
273 post(s)
#07-Sep-22 16:49

Ok, new project. Connected to the SQL database. Opened the drawing. The system remembers the correct projection but the default view starts with degrees ranging from 620 degreesE to 620 degreesW and 445 degrees N to 445 degrees S. Open the table, load all records and select all the records. Go back to the view and zoom to selected. Degrees of the view are correct for northern California which is where the data lies, but still see nothing. So that means we are in problem 1 no? That would square with the fact that an export and re-import fixes the problem. I am having trouble getting the query for the bounding box to work. Is that to be run directly in the database or from V9? I got lost when you said "open the command window for the SQL Server data source." I will do some reading in the manual.

adamw


10,175 post(s)
#08-Sep-22 11:55

Open the table, load all records and select all the records. Go back to the view and zoom to selected. Degrees of the view are correct for northern California which is where the data lies, but still see nothing. So that means we are in problem 1 no?

So, after you zoomed to the exact extent of all objects (by selecting all of them and zooming to the selection), you still see nothing. That means there's something else besides problem 1, possibly problem 2. What happens if you unselect everything, select, say, the first 100 objects in the table window and then zoom to them in the map - can you see them? If not, try the first 10 (unselect everything, select the first 10 in the table, zoom to the selection in the map). I am trying to determine whether we are in situation A: zooming to subsets of objects produces a usable view but zooming to all of them doesn't, or B: zooming to any subset of objects does not produce a usable view. Just in case, could you send a copy of your drawing to tech support? No fields are needed except the geometry.

The queries in my post are meant to be ran on SQL Server. You can run them from a third-party tool (eg, SQL Server Management Studio), or right from 9 - but in 9, in order to run the query using the SQL Server query engine instead of 9's own query engine, you should right-click the SQL Server data source, invoke New Command Window, and run the query in that command window.

Dimitri

7,090 post(s)
#08-Sep-22 12:17

in order to run the query using the SQL Server query engine instead of 9's own query engine, you should right-click the SQL Server data source, invoke New Command Window, and run the query in that command window.

There's a useful example for how to do that in this topic, about switching between Manifold and native query engines,

bclement
273 post(s)
#08-Sep-22 14:44

Thanks Dimitri,

I think I remember watching one of your videos about this, but when I tried to run the query directly on SSMS, it didn't work so I got to wondering if I was supposed to put the non-native prompt in the query first. But then the directions didn't jive with that and it turns out that I somehow missed the whole right-click part of the instruction and the fact that it brings up a command window. Seems I keep missing important bits of information. Might have to have my Manifold drivers license revoked!

bclement
273 post(s)
#08-Sep-22 15:10

Ok, hold everything! I think I have messed something else up that I wasn't even really aware that I could.

So I can't get anything to show at any zoom level with any combination of selections that I tried including a single record. And I noticed that with a single record selected, when I zoom to selection, it seem that the coordinates update, but the zoom coordinates are still over two degrees diagonal in the view window which is obviously wrong.

Now to what has be second-guessing.

In jumping back and forth between my project with the database link and SSMS, I realized yesterday that the two are not in sync. I remember now watching a video where Dimitri said "for to not to" put components that are meant to be local in the server link area. It seems that over the past several weeks, I have gradually been doing just that. In addition, I accidentally named one of my intended-to-be-local tables the same name of one on the database. Fortunately, it did not overwrite the database, but now even if I go to a new instance and use my shortcut to the database, I get the wrong table when I open the table up in the database area. I now wonder if the drawing that I am looking at is even hooked to the right table at all! Refreshing and reconnecting seem to make no difference.

So now the question is, "How do I throw all the junk away and get back to just what is actually in the database?

Very sorry for all the stupidity.

adamw


10,175 post(s)
#08-Sep-22 15:41

You can always right-click the table in the SQL Server data source in the Project pane and create a new drawing for that table. Then perhaps rename it to the name you will remember.

You can also check whether the drawing you were using was tied to the right table by right-clicking that drawing in the Project pane and checking its properties. If the drawing is on the SQL Server data source, the 'Table' property of the drawing should be just a simple table name, eg, '[states]'. If the drawing is in the MAP file that contains the SQL Server data source, the 'Table' property should include the name of the data source, eg, '[sqlserver]::[states]'.

This:

And I noticed that with a single record selected, when I zoom to selection, it seem that the coordinates update, but the zoom coordinates are still over two degrees diagonal in the view window which is obviously wrong.

...surely looks weird. You do see the geometry for the record though, don't you? If you only see it partially or not at all, or if it does not hit the bounds of the window either horizontally or vertically, that looks awfully like a coordinate system issue: the drawing is in coordinate system A, but the map window is in coordinate system B, and the geometry in the drawing is outside of the domain of B or in the area where B has low accuracy. To recheck, when you are in the map window that you are using to zoom, do the two coordinate systems in the Info pane (one for the map, the other for the active layer = the drawing) match?

bclement
273 post(s)
#08-Sep-22 20:26

Sorry all,

I made a poor choice of title for this thread. At first, when I was crafting the original post, I was thinking it was a projection issue. Then I realized that it was not the "typical" projection problem of a map having multiple layers in it with various projections that are not compatible and autoprojection does not recognize one and so you have a "missing" layer in the map. This is a single drawing that does not render. I did not go back and make that clear and I am just now realizing that the word map is appearing repeatedly. I was simply wanting to have a look at the data to see what units were employed in the actual numbers themselves to see if there could be a mismatch between them and the assumed projection that could cause the data not to render. It is also possible that seeing the actual coordinates could show another problem and, most likely, also possible that I wouldn't be able to identify the problem as it is beyond my knowledge. I suspect that my unfortunate title has everyone running in circles that have nothing to do with the problem. The drawing just simply does not render.

LeRepère
152 post(s)
#09-Sep-22 12:47

The drawing just simply does not render.

I made the same observation recently. The attached mxb file contains a "Decoupe" layer that can be seen by opening it individually. It contains a single rectangular polygon.

But, when you include it in a map, it is not displayed. You can't even zoom in on the area covered by the polygon (right click Zoom). It is as if there were no polygon.

Attachments:
Test.mxb

adamw


10,175 post(s)
#09-Sep-22 13:08

Erm. I can see the drawing in the map:

I changed the style to include halo around border to make the area more visible.

Attachments:
decoupe.png

oeaulong

427 post(s)
#09-Sep-22 14:36

I was able to see this behavior. There is something incompatible with the Map / Decoupe projections.

Opening up the file and then the map, no rect appeared. Nor could I select all and then zoom-2-selection.

Trying to reproject Decoupe, it throws an error: "cannot initialize coordinate system or conversion path" Clicking OK, I see a button option for Conversion (currently set to <default>) and chose the epsg:1946 "Molodensky-Badekas" option. The reprojection happens and the rect appears.

I cannot nail down the issue and the projections are foreign to my experience.

LeRepère
152 post(s)
#09-Sep-22 14:56

Okay. In addition.

This is what I just found in my Window10 environment. When I double click on the map or mxb file, it opens in the installed version, .178.0 (10-aug-2022). Everything works fine indeed.

But, when I drop this file in version 178.2 (or open it from File/Open), the problem occurs.

Sloots

603 post(s)
#09-Sep-22 15:21

If I repair the projection to "Canada NAD83 MTM Zone 8" it looks okay to me.


http://www.mppng.nl/manifold/pointlabeler

LeRepère
152 post(s)
#09-Sep-22 17:36

It's same to me when i use Repair initial coordinate system and select EPSG 2950 (same coordinate).

But when i try to Reproject component with EPSG 2950, I get the message

adamw


10,175 post(s)
#10-Sep-22 13:47

The projection of the drawing uses a grid transform. The grid is stored in a file which is normally taken from GRIDS.DAT. Can it be that the install of 9 (178.2) you are using does not have GRIDS.DAT copied to it (put it into EXTRAS)?

LeRepère
152 post(s)
#10-Sep-22 15:28

Adam, this is indeed what was causing the problem. Thank you.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.