logo
down
shadow

How do I use C# and ADO.NET to query an Oracle table with a spatial column of type SDO_GEOMETRY?


How do I use C# and ADO.NET to query an Oracle table with a spatial column of type SDO_GEOMETRY?

By : Logon
Date : November 22 2020, 07:01 PM
wish helps you Here is a link to a post with a sample app using C# and ODP.net to access spatial types.
http://www.orafaq.com/forum/mv/msg/27794/296419/0/#msg_296419
code :


Share : facebook icon twitter icon
sql query to determine if oracle spatial table contains curves

sql query to determine if oracle spatial table contains curves


By : tusac
Date : March 29 2020, 07:55 AM
hop of those help? You can use the TABLE function to extract the sdo_elem_info_array elements, then pivot that and aggregate the resulting rows to yield a row per element, with a column for offset, etype and interpretation.
Something like this should give you your query... (warning: untested)
code :
WITH elem_info AS (
  SELECT 
    TRUNC((ROWNUM - 1) / 3, 0) element_no
  , MAX(DECODE(MOD(ROWNUM, 3), 1, t.COLUMN_VALUE, NULL)) offset
  , MAX(DECODE(MOD(ROWNUM, 3), 2, t.COLUMN_VALUE, NULL)) etype
  , MAX(DECODE(MOD(ROWNUM, 3), 0, t.COLUMN_VALUE, NULL)) interpretation
  FROM my_table tbl
  , TABLE(tbl.shape.sdo_elem_info) t
  GROUP BY TRUNC((ROWNUM - 1) / 3, 0)
)
SELECT DECODE(COUNT(*), 0, 'false', 'true')
FROM elem_info ei
WHERE ei.etype IN (1005, 2005) 
OR ei.interpretation IN (2, 4)
ORACLE: Query a column (type TIMESTAMP) of a table

ORACLE: Query a column (type TIMESTAMP) of a table


By : Vasim mujawar
Date : March 29 2020, 07:55 AM
Does that help If you want data from any day where the time is exactly 17:00:00.000000000
code :
SELECT *
  FROM tableName
 WHERE EXTRACT( hour FROM columnName ) = 17
   AND EXTRACT( minute FROM columnName ) = 0
   AND EXTRACT( second FROM columnName ) = 0
How to insert SDO_GEOMETRY object variable from C# code into oracle table containing coloumn of sdo_geometry type?

How to insert SDO_GEOMETRY object variable from C# code into oracle table containing coloumn of sdo_geometry type?


By : flooow
Date : March 29 2020, 07:55 AM
Does that help I am having an SDO_GEOMETRY object variable that fetches sdo_geometry type data from map. I need to save this data into table as SDO_Geometry field. I was able to successfully fetch SDO_GEOMETRY field from Table and use it , now i am blocked in saving the fetched SDO_Geometry field. , Hi i found the following solution usefull,
code :
OracleParameter endGeometry = cmd.CreateParameter();
endGeometry.OracleDbType = OracleDbType.Object;
endGeometry.UdtTypeName = "MDSYS.SDO_GEOMETRY";
endGeometry.Value = routeSegment.endPointGeometry;
endGeometry.ParameterName = "P_END_GEOM";    

parameter.Add(endGeometry);   
How to insert points coming from sub-query as an object of SDO_GEOMETRY in Oracle spatial database?

How to insert points coming from sub-query as an object of SDO_GEOMETRY in Oracle spatial database?


By : kostas stampoulis
Date : March 29 2020, 07:55 AM
With these it helps I found out a way to insert. There could exist efficient way but this one works fine.
code :
sdo_ordinate_array(

    --this sub-query returns the Longitude of the first point
    (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.X X
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT'), 

    --this sub-query returns the Latitude of the first point
    (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.Y Y
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT'), 

    --this sub-query returns the Longitude of the second point
    (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.X X 
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT'), 

    --this sub-query returns the Latitude of the second point
    (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.Y Y 
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT') 

) --SDO_ORDINATES: co-ordinates of the geomentry
insert into buildings values(
  4, -- index
  'Reading Room', -- building_name
   sdo_geometry(
    2003, --SDO_GTYPE: dltt - 2(2D)0(linear referencing)03(polygon)
    8307, --SDO_SRID: coordinate system
    null, --SDO_POINT: it is for point inserting, if the next two field = null, then it could not be null.
    sdo_elem_info_array( --SDO_ELEM_INFO:
    1, --SDO_STARTING_OFFSET: indicates from which index of the next param of SDO_GEOMETRY would be considered, starts from 1.
    1003, --SDO_ETYPE: 1(exterior, interior  - 2)003(this digits usually comes from SDO_GTYPE)
    3),  --SDO_INTERPRETATION: 1 - simple polygon, 2 - polygon connecting arcs, 3 - rectangle, 4 - circle etc.

    sdo_ordinate_array(

        --this sub-query returns the Longitude of the first point
        (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.X X
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT'), 

        --this sub-query returns the Latitude of the first point
        (SELECT SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.Y Y
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT'), 

        --this sub-query returns the Longitude of the second point
        (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.X X 
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT'), 

        --this sub-query returns the Latitude of the second point
        (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.shape, m.diminfo).SDO_POINT.Y Y 
        FROM buildings c, user_sdo_geom_metadata m 
        WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
        AND c.building_name = 'IICT') 

    ) --SDO_ORDINATES: co-ordinates of the geomentry
 )
);
Oracle: extract data from MDSYS.SDO_GEOMETRY column

Oracle: extract data from MDSYS.SDO_GEOMETRY column


By : Sergio Hita
Date : March 29 2020, 07:55 AM
help you fix your problem I have a table form which I need to extract some information. This table has an oracle spatial (MDSYS.SDO_GEOMETRY) column, from which I also need some data. , You can use sdo_util.getvertices as follows:
code :
select t.x,t.y
  from my_table mt
      ,table(sdo_util.getvertices(mt.positionmap)) t
 where t.id = 2
select t.x,t.y
  from my_table mt
      ,table(sdo_util.getvertices(mt.positionmap)) t
 where t.id = 2
 and mt.positionmap.sdo_gtype=2002
 union all
 select null as X,
        null as Y
 from my_table mt
 where mt.positionmap.sdo_gtype=2001
Related Posts Related Posts :
  • Entity Framework Polymorphism
  • sendmail function shows:Syntax error, command unrecognized. The server response was:
  • WPF - How to bind a DataGridTemplateColumn
  • How to use Linq group a order list by Date
  • Sending SMS using free gateway
  • Support for VB.NET's Imported Namespaces feature in C#
  • Comparing multiple entity properties against list of entities
  • Accessing a master page from httphandler
  • how to increase speed of my execution
  • Hard Coded Paths in a .NET Program
  • .NET sendkeys to calculator
  • MVVM and Ribbon Command
  • Dropdownlist and Datareader
  • How to programmatically self delete? (C# WinMobile)
  • using Linq to partition data into arrays
  • Returning the PK during a linq to sql insert
  • VSTO Alternatives
  • A regular expression that will allow a string with only one Capital Letter
  • object reference not set to an instance of object exception coming at runtime
  • Resizing a monochromatic image in C#
  • How to determine user DN after authentication against an Active Directory?
  • Sharing a COM port over TCP
  • Reading an XML File with .NET
  • Multiplayer game
  • How to equal two strings case sensitively in Linq to SQL?
  • How do you stream text to an IRC Channel
  • How to reduce this IF-Else ladder in c#
  • Integration Test Example With Rhino Mocks
  • My User control belonging to which Design Pattern?
  • How do I extract a substring from a string until the second space is encountered?
  • DataContractJsonSerializer set value extension point
  • I am adding a new splistitem to a sharepoint list but it always gives me an exception: "object reference not set to
  • TextRenderer.DrawText renders Arial differently on XP vs Vista
  • Are there any implementations of multiset for .Net?
  • Multithreading Errors in C#
  • What happens with TCP packets between two Socket.BeginReceive calls?
  • Simple multi-threading - combining statements to two lines
  • Dice face value recognition
  • Unit test class inherited from ContextBoundObject and decorated with ContextAttribute
  • C# design question (Connections)
  • Maintain the SessionID for Entire Application in ASP.NET
  • .NET substitute dependent assemblies without recompiling?
  • C# PDF Control & Library
  • Linq help using "Contains"
  • .net equivalent of htmlunit?
  • required files to distribute a .net application
  • C# Int and math not returning full value
  • Reading XML using XDocument & Linq - check if element is NULL?
  • How do I invoke a MethodInfo that was created from an anonymous method?
  • .Net C# : Read attachment from HttpWebResponse
  • Placing and removing element on array trough object
  • Editing 8bpp indexed Bitmaps
  • How do I draw a graduated border on a polygon using GDI+ via C#/WinForms?
  • Sequential (comb) GUIDs for Oracle
  • How do I separate routes that have the same path but do different things?
  • Which C# / .NET free or standard bits and pieces do I need to make a simple Windows desktop app backed by my database?
  • ReSharper: Can I make my own context action?
  • to avoid page refresh during button click event in asp.net
  • How to deny the move to the next WizardPage in .net?
  • Entity Framework and Modeling Collections with an Interface as a return type
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk