Updating an obj column in MapInfo when the equivalent Oracle sdo_geometry column is updated elsewhere
info...@btinternet.com
map_index_id orig_map_pub_id name_of_feature geographical_coordinates
------------ --------------- --------------- ------------------------
5000840 200016 NORTH STREET MDSYS.SDO_GEOMETRY [1a]
5000841 200016 NORTH STREET QUAD MDSYS.SDO_GEOMETRY [2a]
5000843 200016 OLD STEINE MDSYS.SDO_GEOMETRY [3a]
5000955 200021 NORTH STREET null [1b]
5000956 200021 NORTH STREET QUAD null [2b]
5000957 200021 OLD STEINE null [3b]
info...@btinternet.com
Further to my earlier question, I just wanted to add that when I made
changes in MapInfo and then save my table, it overwrote any
geographical_coordinate column changes I had made. It didn't even comment
that new changes in the database exist and then ask whether I would like to
to overwrite them. It just did it.
Rest assured I made a copy of my table before I carried on working in
MapInfo and tested this out, as I suspected such an issue would occur.
Kind regards
Tim
Peter Horsbøll Møller
info...@btinternet.com
Thank you for your reply. Has anybody else been able to successfully update
geometry columns outside of MapInfo and see the changes within MapInfo?
Having done various tests, which I will explain in detail below, I have now
got the geometry updates to be picked up by MapInfo but only when I update a
non-spatial column from the same row. So in order for MapInfo to pick up my
geometry updates, I am going to create an additional non-spatial column and
update that when I copy the geometry values. This avoids having to update
any other existing columns which do not need updating. I do not understand
why MapInfo will not pick up spatial column changes on there own but this is
what I have found to be happening..
The fuller explanation of what I've done:
When I gave out my table example, I omitted various rows to make the example
fit onto one line. In the process I forgot to include the MI_PRINX column
which is present in my table. The full list of columns are as follows:
mi_prinx
map_index_id
index_type_id
original_map_publication_id
original_map_sheet_number_id
name_of_feature
geographical_coordinates
mapinfo_style_row
When I add new objects in MapInfo and save the table, they are written
correctly back to the Oracle database. This is how I created all the
polygons over one of my maps. As some of the polygons from this map are the
same on other maps, I am then copying them in Oracle, using the script I
already posted.
Peter I tried out your query:
Select * From "USER"."MAP_INDEX_FOR_MAPINFO_USE"
WHERE not GEOGRAPHICAL_COORDINATES Is Null
and it brought in all the rows where I've updated the
geographical_coordinates column using MapInfo but none of the rows where I
had only copied the geographical_coordinates fields in Oracle, using SQL. I
even did this making the database table live and without a cache, yet it
still only brought in those I'd updated in MapInfo.
However as I mentioned above, as soon as I amended a non-spatial field in
Oracle, it picked up the geometry changes in addition to the non-spatial
field changes. I also tested before that whether it would pick up changes to
a non-spatial field whose geometry was not updated and it did.
In a final test, in order to see how updates were working, I updated the
geometry values for three rows, whilst only updating a non-spatial column
for two of the rows.
When I open up the table in MapInfo, using the following SQL:
Select * From "USER"."MAP_INDEX_FOR_MAPINFO_USE"
WHERE not GEOGRAPHICAL_COORDINATES Is Null
AND original_map_publication_id=200010
AND original_map_sheet_number_id=330010
only the two rows whose non-spatial columns were updated, where brought in.
The row where only the geometry was updated was ignored.
Which shows that MapInfo is only picking up geometry changes, outside of
MapInfo, when non-spatial columns are updated. It is however always picking
up non-spatial column updates, outside of MapInfo, regardless of whether
spatial-columns are amended or not.
This may be a problem which has been resolved since version 8. I would be
grateful for any other views as to whether others here have experienced
similar issues as this. There is a solution to my problem, which is
fantastic news for my work. However it's not something I feel I should be
having to do in the first place, no matter how pleased I am to have found a
solution.
Kind regards
Tim
----- Original Message -----
From: "Peter Horsbøll Møller" <mapi...@horsboll-moller.dk>
To: <mapi...@googlegroups.com>
Sent: Sunday, November 02, 2008 6:37 PM
Subject: [MI-L] Re: Updating an obj column in MapInfo when the equivalent
Oracle sdo_geometry column is updated elsewhere
info...@btinternet.com
Has anyone else had any experience of working in MapInfo and Oracle at the
same time? What is the best way to make sure MapInfo doesn't wipe over ones
Oracle database tables with data that is out of date? That happened to me
last night/this morning. I have been doing refresh my DBMS table in MapInfo
and I am sure I closed all my tables after doing this and not before.
If I disconnect from my database and then reconnect again, with the MapInfo
tables still open, will that cause problems? Is there a step I am missing
out or am I doing something in the wrong order without realising it?
Kind regards
Tim
info...@btinternet.com
I have found out what the problem is but not a good solution. When I created
a new non-spatial column in Oracle, polygon_copied_in_oracle, and then ran a
script which put Y in the column, everytime I updated the geometry column,
MapInfo wasn't picking up the changes!!! This is why it is wiping my data.
It doesn't see any data changes there. I was only updating the non-spatial
column because I believe that was how it would pick up the spatial column
changes.
For some reason is does pick up changes I make to existing columns but as I
don't need to update my existing columns that is not very helpful to me. I
may be able to populate my polygon_copied_in_oracle column with N and remake
my table mappable in MapInfo to see if then changing N to Y, will work but
it may not.
If anyone has any other ideas I would be grateful. I cannot believe my copy
is the only copy where this problem occurs. As MapInfo do not provide very
good online technical documents, I can't find any information about this
online. I wonder how many other undocumented software features exist in
MapInfo Professional 8?
Kind regards
Tim
info...@btinternet.com
I have now found out what was causing the problem and what the solution is.
When I first opened my table in MapInfo, I choose certain columns to bring
in. I then set up my map windows and table layouts before saved my
workspace. I have been using this Workspace ever since. I have not at any
stage closed my Oracle table and brought it in again with additional columns
because I had all the columns I needed in order to do my work in MapInfo.
However it turns out that when I add a new column into Oracle, unless I
reimport my table into MapInfo with this column, MapInfo ignores any updates
made on that column. Which means when you refresh you table, it doesn't know
anything has been updated in that column. However when you save your table,
it still manages to save over the column and you loose your data.
This is obviously why it is not recognising my sdo_geometry column updates
outside of MapInfo because my sdo_geometry column is called
geographical_coordinates and not obj, which is the MapInfo name for the
column. MapInfo knows that when you make a change to your obj column and
save that data, this data needs to be written back from obj to
geographical_coordinates. However when you refresh your table, it does not
writing the data from geographical_coordinates back to obj. It is only doing
the other way round. By changing my Oracle column name to obj, it would pick
up the changes because it is at least checking for columns with the same
name and then looking for their changes.
To make this even more confusing, it is recognising column updates for
columns not brought into MapInfo, if a column value for a column within
MapInfo is updated for that row. The update on this column can be done
outside of MapInfo. I hope that makes sense.
The reason I had not changed my geographical_coordinates column name to obj
was because I do not remember reading anything which says I must do this.
Besides I prefer to use names which refer to what I am doing. I think obj is
a bit vague. obj for what?
Eric_Bl...@mapinfo.com
Tim,
I am not sure I understand the steps that reproduce this issue. I would suggest that you should suggest a reproducible example to Tech Support as quickly as possible.
However, I will tell you this:
- Professional does not require that a spatial column be named anything in particular. The name of the spatial column is obtained from the MapInfo MapCatalog which we do require for access. It may very well be possible that if a column is named "obj" that we automatically recognize this as spatial. I cannot confirm or deny that. However, if the catalog has a name we will use it.
- If the spatial column is added after the set of columns to be accessed has been defined, then Professional should ignore that column. If we are updating (with null?) a column we should not even be accessing,then that would certainly be a bug.
Eric Blasenheim
Chief Product Architect
Pitney Bowes Business Insight [MapInfo]
Mail List:grbounce-yvy1equaaaajbprysysrydkk7vpghp_9=mail_list=mapin...@googlegroups.com
| From: | <info...@btinternet.com> on 11/05/2008 12:00 PM GMT |
| To: | <mapi...@googlegroups.com> |
| cc: | |
| Subject: | [MI-L] Re: MapInfo is copying over data in my Oracle database tables with out of date data |
info...@btinternet.com
Thank you for your e-mail. I am a student so I don't know if I can
personally contact technical support or whether my lectuers/university
support staff have to do it on my behalf. They have only just return from
being away so I will enquire with them. Whilst they were away I thought I
would ask for advice on this list.
Below though is the full example of what I have been doing. I apologise for
those who have read some of this already. I feel that it is important that
problems I am having get archived on the Internet, regardless of whether I
am in touch with technical support or not, so that should anyone else have a
similar problem, they can find information about it on the Internet. Even if
at the end it says contact technical support, it is useful to have such
examples. I do such things all the time when working in Oracle. It aids my
understanding if I can see other peoples examples or even errors. One can
learn from other peoples mistakes, which is why e-mail groups such as list
and online forums for software, are so useful.
I want to make it clear one can name a geographical column in Oracle
anything they like. However I am finding that the names I have been giving
it so far, have not worked in the example am I going to list.
Today I noticed that when you choose the fields to bring in with your table
it has the column name OBJECT and not GEOGRAPHICAL_COORDINATES which is the
name of my SDO_GEOMETRY column. However when I go to do a select SQL query,
the list of columns for my table includes obj and not OBJECT. I am not sure
which name is the correct column name in MapInfo. All my other non-spatial
column names appear as they are in Oracle.
What I have done is as follows. Firstly I created entered all the correct
information into Oracle in order to be able to make my table mappable. Then
I made the table, MAP_INDEX_FOR_MAPINFO_USE mappable in MapInfo. This
worked. I won't quote all the commands for this.
The my list of columns is as follows:
mi_prinx
map_index_id
index_type_id
original_map_publication_id
original_map_sheet_number_id
name_of_feature
geographical_coordinates -- the spatial column
mapinfo_style_row
All those columns were brought into MapInfo.
I brought in various raster maps covering the same area, which were stored
on my local hard drive. I selected a row in my table containing a street
name, contained in the name_of_feature column, that covered an area relating
to one of the maps which I wished to draw over.
Then I went to my map window and draw over that street. This then stored the
polygon with that row of data. I deselected that row and moved onto another
street. I did this for all streets in the index that referred to that map.
As many of the streets were the same on other maps I decided I would simply
copy the information from this map to another fields in the column that:
a) referred to the same street;
b) referred to a different map.
Here is a copy of my script:
-- undefine and define name of feature being updated
UNDEFINE name_of_feature_to_be_updated
----------------------------
--copy the feature polygon--
----------------------------
-- undefine and define name of feature which will be used
UNDEFINE name_of_feature_to_be_used
DEFINE B.name_of_feature = '&&name_of_feature_to_be_used'
UPDATE MAP_INDEX_FOR_MAPINFO_USE B
SET (B.geographical_coordinates, B.mapinfo_style_row) =
(SELECT A.geographical_coordinates,
A.mapinfo_style_row
FROM MAP_INDEX_FOR_MAPINFO_USE A
WHERE A.original_map_publication_id=200016
AND A.name_of_feature=UPPER('&&name_of_feature_to_be_used'))
WHERE B.original_map_publication_id=200010
AND B.original_map_sheet_number_id=330010
AND B.name_of_feature=UPPER('&&name_of_feature_to_be_updated');
--------------------------
--undefine the variables--
--------------------------
UNDEFINE name_of_feature_to_be_used
UNDEFINE name_of_feature_to_be_updated
Using the above script, which I ran in SQL Developer, I type in a street
name that was to be updated. Then I type in the name of the street that I
wished to update. This then copied the values to the correct field. As I
have already set the map publication and sheet numbers, only 1 row was
copied and it was always copied to the map where it was required.
Once I have done this I then go to MapInfo and select Refresh DBMS table...
Next I go to the row(s) where I have copied my the GEOMETRY values to find
that nothing had appeared in that field. Whether the table was stored
locally or live, it did not show the updates. It was in Oracle because
running the following command brought up the data:
-------------------------------
--Check what has been written--
-------------------------------
SELECT substr(name_of_feature,1,30)"name_of_feature",
substr(A.original_map_publication_id,1,10)"orig_m_pub_id",
substr(A.original_map_sheet_number_id,1,10)"orig_m_sh_no_id",
substr(mapinfo_style_row,1,10)"MI_style",
substr(A.polygon_copied_in_oracle,1,9)"poly_copy",
A.geographical_coordinates
FROM MAP_INDEX_FOR_MAPINFO_USE A
WHERE A.name_of_feature=UPPER('&&name_of_feature_to_be_updated')
AND A.original_map_publication_id=200010
AND A.original_map_sheet_number_id=330010;
After spend some hours figuring out what was happening, I noticed that if I
updated a column outside of MapInfo but one that had also been brought into
MapInfo, the updates appeared in MapInfo.
So if I had a street called Bond Street and in SQL developer I changed the
name to Bonded Street, Bonded Street update would appear in my MapInfo
table, where it was a copy or live. If I then updated my GEOMETRY column at
the same time, again in SQL developer, the GEOMETRY column would get updated
in MapInfo.
So with that in mind I created an additional non-spatial column in my
database called COPIED_POLYGON_IN_ORACLE. Every time I ran the update
command on my geometry column I also ran the following:
---------------------------------------------------------------
--update polygon_copied_in_oracle but only if geometry exists--
---------------------------------------------------------------
UPDATE MAP_INDEX_FOR_MAPINFO_USE B
SET (B.polygon_copied_in_oracle) ='Y'
WHERE B.original_map_publication_id=200010
AND B.original_map_sheet_number_id=330010
AND B.name_of_feature=UPPER('&&name_of_feature_to_be_updated')
AND NOT B.geographical_coordinates is NULL;
I did not bring this new column into MapInfo because I did not require it in
MapInfo. However I thought at this stage that updating any non-geometry
column, would cause my geometry updates to appear in MapInfo.
However I soon found this was not the case. I even found that any updates I
had made to any columns not in MapInfo, were wipped when I saved my MapInfo
version of the table. In this case it was only locally stored copy that I
was using.
Which is why I now believe calling a geometry column in MapInfo by it's
MapInfo column name, will mean that updates will exist. I have not been able
to test this out yet because I am having to create some test tables in
Oracle first so that I don't accidentally wipe what I have already done in
MapInfo.
If anyone knows whether the name for a geographical column in MapInfo is obj
or OBJECT, I would be grateful to know. If anyone knows how I can get the
column to display GEOGRAPHICAL_COORDINATES, which is the column name that I
use in Oracle, I would also be grateful.
Whilst none of what I have written, suggests there is a bug as such, it does
suggest that there is a way of working which is not documented, which is why
I used the term undocumented software feature. If there is anything I have
written that people do not understand, then please let me know and I will
try and explain it in more detail.
Kind regards
Tim
--- On Wed, 5/11/08, Eric_Bl...@mapinfo.com
<Eric_Bl...@mapinfo.com> wrote:
From: Eric_Bl...@mapinfo.com <Eric_Bl...@mapinfo.com>
Subject: [MI-L] Re: MapInfo is copying over data in my Oracle database
tables with out of date data
To: mapi...@googlegroups.com
Date: Wednesday, 5 November, 2008, 1:32 PM
Tim,
I am not sure I understand the steps that reproduce this issue. I would
suggest that you should suggest a reproducible example to Tech Support as
quickly as possible.
However, I will tell you this:
1.. Professional does not require that a spatial column be named
anything in particular. The name of the spatial column is obtained from the
MapInfo MapCatalog which we do require for access. It may very well be
possible that if a column is named "obj" that we automatically recognize
this as spatial. I cannot confirm or deny that. However, if the catalog has
a name we will use it.
2.. If the spatial column is added after the set of columns to be
info...@btinternet.com
Thank you for your help. After talking to someone at my university with a
greater understanding of SQL than I have, I have now solved my problems. It
was a simple mistake that people starting out in SQL might make. I never hit
the commit button in SQL Developer whenever I ran my updating script.
I was using the commit button every time I viewed a table tab and deleted
rows of data but not when I ran my updating script. I just thought running
the script committed my changes to the database but in fact all it was doing
was committing changes for the SQL session I had running. That explains why
MapInfo could not see the changes and why some changes disappeared when I
restarted SQL Developer.
I had been using the commit button when I deleted rows after opening a table
tab because that is what I thought the commit button was used for, along
with things like packages and procedures. Therefore some updates were being
committed. Enough though to make me think that my changes were being
committed when I ran my scripts.From now on I will add the SQL command
commit to all my scripts. That why I won't make the same mistake again.
I do think it would have been helpful if my column name
geographical_coordinates was shown in MapInfo as geographical_coordinates
and not OBJECT or obj. So thank you for your help. I hate making simple
mistakes but that is what happens when one is learning something that's new
to them.
info...@btinternet.com
Just a quick update to say when I select my columns to bring them into
MapInfo I am getting the correct column name for my Oracle column. It is
only when I am working within MapInfo that I see obj and not the Oracle
column name as I might expect.
Eric_Bl...@mapinfo.com
Tim,
As I mentioned, the name of the geometry column in Oracle can be whatever you like as long as it is registered in the MapCatalog. However, it is true that in MapBasic and Professional the one and only name for the geometry column is obj (object also works in certain places). The MapCatalog is our indirection that allows us to translate the generic name "obj" into the actual name when we communicate with the server. Another way of saying this is that "obj" is the name of the geometry column inside Professional regardless of what the data source is (native TAB, Access, Oracle, SpatialWare, etc.)
Eric
Mail List:grbounce-yvy1equaaaajbprysysrydkk7vpghp_9=mail_list=mapin...@googlegroups.com
| From: | <info...@btinternet.com> on 11/06/2008 04:15 PM GMT |