Hi @kamepalli, do you have an ID in the CSV file that can be used to match features in the Oracle table?
@kamepalli I might be reading into this too much... you mention "each and every coordinate". Are you dealing with more than just points here? Are you wanting to take, for example, a line feature and update some of the vertices but not all that make of that line feature (from the csv file)? Or is it just the an entire geometry that you'll be updating?
Tia's question is relevant either way.
If you have an ID in the CSV file than this should be trivial.
@TiaAtSafe @SteveAtSafe thank you for your responses. Yes I can use a combination of fields to match features from both Oracle and the CSV fie. Steve, you are right in assuming that we would sometime change some of the vertices of a linear or polygon geometries but not the whole geometry. Is it still a possibility ?
Thank you for your time.
Yes, updating geometries in oracle spatial can be done when you have same ID in both CSV and Oracle spatial data either with normal update statement in sql executor or other methods...
Yes, updating geometries in oracle spatial can be done when you have same ID in both CSV and Oracle spatial data either with normal update statement in sql executor or other methods...
@kamepalli, I would try using the SQLExecutor as @pratap has suggested. The Oracle Writer itself can be used to perform updates as well (you can set whether to update spatial columns or not.
@kamepalli, I would try using the SQLExecutor as @pratap has suggested. The Oracle Writer itself can be used to perform updates as well (you can set whether to update spatial columns or not.
@TiaAtSafe I am a beginner and I am not sure about too many options in FME. I've come across a post where a changedetector is used to pick up the changes and update destination. Which is a better choice ? SQL executor or a Changedetector ? also, would you help me point to some references?
@TiaAtSafe I am a beginner and I am not sure about too many options in FME. I've come across a post where a changedetector is used to pick up the changes and update destination. Which is a better choice ? SQL executor or a Changedetector ? also, would you help me point to some references?
Thank you for those references @TiaAtSafe Having a sample workspace will help a lot.
Hi @kamepalli, you should experiment with setting the Writer mode to update. I've used the OBJECTID as my Match Key. If you need a combination of attributes to create a unique key, you'll need to specify that.
Depending on how the data is actually stored, you can go about updating in different ways.
I'm not sure how the data is stored in your CSV, but the translation can be as easy as a Reader & a Writer.
Of course there are multiple ways to do this, this is just one of the ways.
Hi @kamepalli, you should experiment with setting the Writer mode to update. I've used the OBJECTID as my Match Key. If you need a combination of attributes to create a unique key, you'll need to specify that.
Depending on how the data is actually stored, you can go about updating in different ways.
I'm not sure how the data is stored in your CSV, but the translation can be as easy as a Reader & a Writer.
Of course there are multiple ways to do this, this is just one of the ways.
Thank you @TiaAtSafe. I now have the data in a shape file so that the coordinates are now represented as a geometry itself. I will try to create a writer with a combination of columns as key and see how it goes.
@TiaAtSafe @SteveAtSafe thank you for your responses. Yes I can use a combination of fields to match features from both Oracle and the CSV fie. Steve, you are right in assuming that we would sometime change some of the vertices of a linear or polygon geometries but not the whole geometry. Is it still a possibility ?
Thank you for your time.
@kamepalli . That isn't trivial to do. If you are really good with SQL and working with the SDO_GEOMETRY object you could possible do it... you'd have to know the ordinate array, the position of the vertex in the array and then you could do the update. I'm not well versed in this.
Here is an example of what I'm talking about where they are updating the X coord of an SDO_POINT structure...click here for
article. You'll notice this refers to the SDO_POINT and this isn't the same for a polygon or linear feature. You might be able to find an example of this. I'll chat with a colleague to see if they have an idea of what it would look like in FME.
SQLExecuter will be your friend.
@kamepalli . That isn't trivial to do. If you are really good with SQL and working with the SDO_GEOMETRY object you could possible do it... you'd have to know the ordinate array, the position of the vertex in the array and then you could do the update. I'm not well versed in this.
Here is an example of what I'm talking about where they are updating the X coord of an SDO_POINT structure...click here for
article. You'll notice this refers to the SDO_POINT and this isn't the same for a polygon or linear feature. You might be able to find an example of this. I'll chat with a colleague to see if they have an idea of what it would look like in FME.
SQLExecuter will be your friend.
Thank you very much for the info @SteveAtSafe I would have thought so. any pointers to this is greatly appreciated. So far I have tested point geometries and they seem ok. However, I used a shape file instead a CSV and using a simple writer with feature operation set to update. I will check this approach with linear geometries and see how it works.
However the field length of the shape file is being set to fixed 10 characters I have issues matching the columns. I will have to go for a mapinfo file and see if that solves this new issue.
Hi @kamepalli, you should experiment with setting the Writer mode to update. I've used the OBJECTID as my Match Key. If you need a combination of attributes to create a unique key, you'll need to specify that.
Depending on how the data is actually stored, you can go about updating in different ways.
I'm not sure how the data is stored in your CSV, but the translation can be as easy as a Reader & a Writer.
Of course there are multiple ways to do this, this is just one of the ways.
Hi @TiaAtSafe as I notified in my previous comments I have now converted data from CSV to a shape file so that the geometry type conversions are not required. Now the geometry in shape and oracle are of same types. I used option 1 and created a reader to shape and a writer to oracle with a combined column identifier. So far so good. I've tested few records and the geometries updated to the ones in shape file.
I will do more updates and testing to see if this process works. Once again thank you for the help.
