Hi,
You really should convert your X and Y into SDO_GEOMETRY. Oracle Locator is free and it is enough for Geoserver. If your data are maintained so that X and Y are updated as plain numbers, make triggers to convert them automatically into SDO_GEOMETRY. But if you definitely want to continue with X and Y perhaps you can make a SQL view by using parts of the following SQL as template. This SQL creates a new spatial table from X and Y, registers it into geom_metadata and creates the spatial index. It is untested but it must be close to working.
drop table new_spatial;
CREATE TABLE new_spatial
AS
SELECT
CASE WHEN NVL(coord_X,0)=0 OR NVL(coord_Y,0)=0 THEN NULL
ELSE
MDSYS.SDO_GEOMETRY(2001,
4326,
MDSYS.sdo_point_type(s.coord_X,
s.coord_Y, NULL),
NULL, NULL) END GEOM_COLUMN,
FROM source_table s;
INSERT INTO user_sdo_geom_metadata
(table_name, column_name, diminfo, srid)
SELECT ‘NEW_SPATIAL’, ‘GEOM_COLUMN’,
MDSYS.sdo_dim_array (MDSYS.sdo_dim_element (‘X’,
MIN (s.x) - 100000,
MAX (s.x) + 100000,
1/200
),
MDSYS.sdo_dim_element (‘Y’,
MIN (s.y) - 100000,
MAX (s.y) + 100000,
1/200
)
),
NULL
FROM new_spatial nt,
TABLE (sdo_util.getvertices (sdo_geom.sdo_mbr (nt.GEOM_COLUMN))) s
;
create index new_spatial_SPIDX
on new_spatial(GEOM_COLUMN)
indextype is mdsys.spatial_index;
-Jukka Rahkonen-
···
Jonathan Moules wrote:
Hi,
You might be able to do it using “geomFromWKT” if you convert the data into WKT.
http://docs.geoserver.org/stable/en/user/filter/function_reference.html?highlight=filter
Alternately possibly using a SQL View where the conversion is done in the database:
http://docs.geoserver.org/stable/en/user/data/database/sqlview.html?highlight=sql%20view
But that’ll require figuring out how to write an Oracle query that does that.
Jonathan
On 15 October 2013 10:26, cesare gerbino <cesaregerbino@anonymised.com> wrote:
Hi all!.
I’ve a table in ORACLE (no spatial), with colums in which there are points coordinates and
I’d like use this data to create a layer in GeoServer.
Is there any manner to use this data directly form GeoServer or I’ve to transform the data in shp or text file and then use it?
Thank you very much in advance
Cesare Gerbino
http://cesaregerbino.wordpress.com/
http://www.facebook.com/cesare.gerbino
http://www.facebook.com/pages/Cesare-Gerbino-GIS-Blog/246234455498174?ref=hl
https://twitter.com/CesareGerbino
http://www.linkedin.com/pub/cesare-gerbino/56/494/77b
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135031&iu=/4140/ostg.clktrk
Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
This transmission is intended for the named addressee(s) only and may contain sensitive or protectively marked material up to RESTRICTED and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation.