Re: [Geoserver-users] Creating a spatial view from an XY table in MS SQL Server?
Ok, figured it out. The view as orginally defined used
"geography::STGeomFromText". GeoServer only recognizes the "geometry"
MS-SQL datatype (which is reasonable). Changing the view to use
"geometry::STGeomFromText" fixed the problem. GeoServer recognizes the
geometry column and renders it.
Furthermore, the GEOMETRY_COLUMNS metatable is not required, which is a
nice simplification.
Probably we should define a spatial functional index as well, but since
this is a Point layer with only a 100 or so records, performance is fine
even without it.
Another user reported that this even works with defining the view as a
GeoServer SQL View, which is a very handy capability. It would be nice if
this worked for Oracle as well!
On Mon, Feb 6, 2017 at 10:01 AM, Martin Davis wrote:
> Has anyone tried creating a spatial view on an XY table in MS SQL Server,
> so it can be exposed via GeoServer?
>
> We are not having any luck with this. What we have done is:
>
> 1) Define a spatial view:
>
> CREATE VIEW UDO_Fuel_Cache_SVW AS
>
> SELECT
>
> ID,
>
>Latitude,
>
>Longitude,
>
>Geographic,
>
>Fuel_Type,
>
>Updated,
>
>Full_Barrels,
>
>Partial_Barrels,
>
>Empty_Barrels,
>
>Owner,
>
>ContactPhone,
>
>Comments,
>
>FireCentre,
>
>FireZone,
>
>geography::STGeomFromText('POINT('+convert(varchar(20),Longitude)+' '+
> convert(varchar(20),Latitude)+')',4326) AS Geom
>
> FROM Incident.dbo.UDO_Fuel_Cache
>
>
> 2) Define a geometry metadata table:
>
> CREATE TABLE GEOMETRY_COLUMNS(
>
>F_TABLE_SCHEMA VARCHAR(30) NOT NULL,
>
>F_TABLE_NAME VARCHAR(30) NOT NULL,
>
>F_GEOMETRY_COLUMN VARCHAR(30) NOT NULL,
>
>COORD_DIMENSION INTEGER,
>
>SRID INTEGER NOT NULL,
>
>TYPE VARCHAR(30) NOT NULL,
>
>UNIQUE(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN),
>
>CHECK(TYPE IN ('POINT','LINE', 'POLYGON', 'COLLECTION', 'MULTIPOINT',
> 'MULTILINE', 'MULTIPOLYGON', 'GEOMETRY') ));
>
> go
>
> INSERT INTO GEOMETRY_COLUMNS
>
> ( F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID
> , TYPE )
>
> VALUES ('', 'UDO_Fuel_Cache_SVW', 'Geom', 2, 4326, 'POINT');
>
>
> 3) Configure the SQL Server Store to refer to GEOMETRY_COLUMNS
> 4) Configure the layer (I hope correctly). The layer featuretype listing
> shows a column Geom of type byte[] - which doesn't seem promising.
>
> When trying to view the layer, we get the error:
>
> 2017-02-06 09:44:47,833 ERROR [org.geotools.jdbc] - Failed to execute
> statement SELECT FROM "UDO_Fuel_Cache_SVW"
> Caused by: java.sql.SQLException:
> com.microsoft.sqlserver.jdbc.SQLServerException:
> Incorrect syntax near the keyword 'FROM'.
>
>
>
>
>
>
>
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
[Geoserver-users] Error when creating SQL view with parameters via REST
Hello, I am running into an error when creating an SQL view data layer via REST. The problem is encountered when trying to creating the SQL view parameter. I submit the following text below in my JSON object and PosgreSQL detects the '%' and it creates the error so no view is created. select gi_id::text || '_0'::text as fake_id, gi_id AS id, short_label, geom, feat_label, 0 as zoom from terrascope.sgl_boundaries_view where sgl_id = %id% I am using Geoserver 2.8, here is the stack trace. Request: getServiceInfo 2017-02-07 09:32:15,570 ERROR [geoserver.ows] - org.geoserver.wfs.WFSException: Error occurred getting features at org.geoserver.wfs.GetFeature.run(GetFeature.java:651) at org.geoserver.wfs.DefaultWebFeatureService.getFeature(DefaultWebFeatureService.java:122) at sun.reflect.GeneratedMethodAccessor570.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.geoserver.ows.util.RequestObjectLogger.invoke(RequestObjectLogger.java:55) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at com.sun.proxy.$Proxy56.getFeature(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:845) at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:275) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778) at javax.servlet.http.HttpServlet.service(HttpServlet.java:620) at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:28) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:75) at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:71) at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:71) at org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:50) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311) at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73) at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.
Re: [Geoserver-users] Support for WFS Custom Dimensions
Hi, custom dimension support for vector layers is not implemented yet, you can have a look here for getting it implemented: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer Mind, WFS wise there is no such a thing as dimension support in the protocol, dimensions would still only show up in WMS, in WFS they would be just filters you have to send over. Unless GeoServer implements its own vendor extensions to have dimensions as first class citizens in WFS too. Cheers Andrea On Tue, Feb 7, 2017 at 4:41 PM, Wilwerding, Jerry wrote: > I need to expose weather forecast data in WMS. The data is raster and > feature. Since it is forecast data, two time dimensions are being > leveraged: reference_time and a forecast_period. > > > > I have a solution for the raster data since WCS supports custom > dimensions. The WCS custom dimensions can be exposed via WMS. > > > > Can custom dimensions exist for WMS layers that use feature data? If so, > could a reference be provided? > > > > Thanks! > > > > Jerry Wilwerding > > > > > -- > Check out the vibrant tech community on one of the world's most > engaging tech sites, SlashDot.org! http://sdm.link/slashdot > ___ > Geoserver-users mailing list > Geoserver-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geoserver-users > > -- == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it *AVVERTENZE AI SENSI DEL D.Lgs. 196/2003* Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003. The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc. --- -- Check out the vibrant tech community on one of the world's most engaging tech sites, SlashDot.org! http://sdm.link/slashdot___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
[Geoserver-users] Support for WFS Custom Dimensions
I need to expose weather forecast data in WMS. The data is raster and feature. Since it is forecast data, two time dimensions are being leveraged: reference_time and a forecast_period. I have a solution for the raster data since WCS supports custom dimensions. The WCS custom dimensions can be exposed via WMS. Can custom dimensions exist for WMS layers that use feature data? If so, could a reference be provided? Thanks! Jerry Wilwerding -- Check out the vibrant tech community on one of the world's most engaging tech sites, SlashDot.org! http://sdm.link/slashdot___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Caching beyond the bounds of a UTM Zone
Hi, so, finally I found a solution although I still don't know the cause: After some searching on the server I found out that below $GEOSERVER_DATA_DIR/gwc_layers a xml-config-file for each gwc-layer is created. For the problematic layer the corresponding xml-file had a wrong extend. After deletion of the xml-file a restart of the tomcat was necessary as GWC was throwing an exception when one of the web-GUIs of the integrated GWC was accessed. After restarting tomcat I had to reactivate "Create a cached layer" in the "Cache-Tab" of the specific layer (I do not know the real names of the check boxes and tabs as my Geoserver-GUI is in German (BTW is there a way to switch the GUI to another language?)). After "Save" the xml-config-file for the layer was created again in $GEOSERVER_DATA_DIR/gwc_layers. Up to here the XML-file has no extend-information but the gridSetName is already defined. I then created the cache and the result was as expected. I hope this might help somebody in the future. Is this seen as a bug and should I file a ticket? I can't actually tell when and why the wrong extend made its way in the config-file. Here's a part of output of diff -u "wrong xml" "correct xml": - 239323.44497139292 + 20.0 5129639.24594039 - 812763.4449713929 + 1032886.6793141605 620.0 Regards Torsten -Ursprüngliche Nachricht- Von: Drey, Torsten Gesendet: Dienstag, 7. Februar 2017 10:28 An: geoserver-users@lists.sourceforge.net; 'Julian Hollingbery' Betreff: AW: [Geoserver-users] Caching beyond the bounds of a UTM Zone Hi Julian, yes, you are right that I should be careful with setting the alignement. Unfortunately, it doesn't change the behavior. I changed the to "true", but the effect still is there. BTW, the effect is also there when I use the integrated demo from GWC. I then also tested another layer (just in case..), the result is all the same. What also seems strange to me is that I can query the not-displaying tiles in the GWC-Demo. Here is another screenshot which is documenting this: https://drive.google.com/open?id=0B0Id8TV87m2qU1cyNGxtQ1pjLUk You can see the pointer used for querying and the result below the map. So, the data is somehow present but not displaying. Does this behavior maybe help to identify the cause? Or is this just because the GetFeatureInfo is handled by Geoserver? In general I assume the set up is fine as the layer is cached and displaying correctly in the scales 1:4M and 1:2M. I just don't see a reason for not caching correctly in greater scales. Best regards Torsten -Ursprüngliche Nachricht- Von: Julian Hollingbery [mailto:jul...@northtech.dk] Gesendet: Montag, 6. Februar 2017 17:02 An: Drey, Torsten; geoserver-users@lists.sourceforge.net Betreff: SV: [Geoserver-users] Caching beyond the bounds of a UTM Zone Hi Torsten, I notice that you have false I don't have that tag in my geowebcache.xml at all. Mind you, it is quite a lot of versions since I configured my GWC.. Reading http://geowebcache.org/docs/current/concepts/gridsets.html#from-gridsets-to-tiles, I wonder if you are using WMTS in QGIS, and it might make a difference for you setting it to "true" or omitting it? Regards, Julian -Oprindelig meddelelse- Fra: torsten.d...@t-systems.com [mailto:torsten.d...@t-systems.com] Sendt: 6. februar 2017 16:19 Til: geoserver-users@lists.sourceforge.net Emne: Re: [Geoserver-users] Caching beyond the bounds of a UTM Zone Hi, thanks for your reply Julien; it helped to know that it should work in general! I made some progress regarding the caching of a layer in utm zone 32 and 33 but it doesn't work out completely. What I did is to truncate the old cache and create a new grid set. I also changed the bounds a little bit. Here's an extract from geowebcache.xml: 25832 20.0 510.0 130.0 620.0 false 400.0 200.0 100.0 50.0 25.0 10.0 5.0 25000.0 1.0 5000.0 2500.0 What is working: For the scales 1:4.000.000 and 1:2.000.000 the tiles are generated as expected. What is not working: For every larger scale tiles are missing in the east. It's probably best described with some screenshots: I did the screenshots from inside QGIS. On top of the hillshaded dem (in grey; this is the one to be tiled, loaded as WMTS in QGIS) is a layer of germany (in blue, just to make things clearer). Here is correct example for 1:2.000.000: https://drive.google.com/open?id=0B0Id8TV87m2qSFRZOGRPaDg0TGs Here is one for 1:1.000.000: https://drive.google.com/open?id=0B0Id8TV87m2qYnk1S1Z5dk4tSHM You can already see that tiles are missing in the east. And finally one for 1:500.000: https://drive.google.com/open?id=0B0Id8TV87m2
Re: [Geoserver-users] Caching beyond the bounds of a UTM Zone
Hi Julian, yes, you are right that I should be careful with setting the alignement. Unfortunately, it doesn't change the behavior. I changed the to "true", but the effect still is there. BTW, the effect is also there when I use the integrated demo from GWC. I then also tested another layer (just in case..), the result is all the same. What also seems strange to me is that I can query the not-displaying tiles in the GWC-Demo. Here is another screenshot which is documenting this: https://drive.google.com/open?id=0B0Id8TV87m2qU1cyNGxtQ1pjLUk You can see the pointer used for querying and the result below the map. So, the data is somehow present but not displaying. Does this behavior maybe help to identify the cause? Or is this just because the GetFeatureInfo is handled by Geoserver? In general I assume the set up is fine as the layer is cached and displaying correctly in the scales 1:4M and 1:2M. I just don't see a reason for not caching correctly in greater scales. Best regards Torsten -Ursprüngliche Nachricht- Von: Julian Hollingbery [mailto:jul...@northtech.dk] Gesendet: Montag, 6. Februar 2017 17:02 An: Drey, Torsten; geoserver-users@lists.sourceforge.net Betreff: SV: [Geoserver-users] Caching beyond the bounds of a UTM Zone Hi Torsten, I notice that you have false I don't have that tag in my geowebcache.xml at all. Mind you, it is quite a lot of versions since I configured my GWC.. Reading http://geowebcache.org/docs/current/concepts/gridsets.html#from-gridsets-to-tiles, I wonder if you are using WMTS in QGIS, and it might make a difference for you setting it to "true" or omitting it? Regards, Julian -Oprindelig meddelelse- Fra: torsten.d...@t-systems.com [mailto:torsten.d...@t-systems.com] Sendt: 6. februar 2017 16:19 Til: geoserver-users@lists.sourceforge.net Emne: Re: [Geoserver-users] Caching beyond the bounds of a UTM Zone Hi, thanks for your reply Julien; it helped to know that it should work in general! I made some progress regarding the caching of a layer in utm zone 32 and 33 but it doesn't work out completely. What I did is to truncate the old cache and create a new grid set. I also changed the bounds a little bit. Here's an extract from geowebcache.xml: 25832 20.0 510.0 130.0 620.0 false 400.0 200.0 100.0 50.0 25.0 10.0 5.0 25000.0 1.0 5000.0 2500.0 What is working: For the scales 1:4.000.000 and 1:2.000.000 the tiles are generated as expected. What is not working: For every larger scale tiles are missing in the east. It's probably best described with some screenshots: I did the screenshots from inside QGIS. On top of the hillshaded dem (in grey; this is the one to be tiled, loaded as WMTS in QGIS) is a layer of germany (in blue, just to make things clearer). Here is correct example for 1:2.000.000: https://drive.google.com/open?id=0B0Id8TV87m2qSFRZOGRPaDg0TGs Here is one for 1:1.000.000: https://drive.google.com/open?id=0B0Id8TV87m2qYnk1S1Z5dk4tSHM You can already see that tiles are missing in the east. And finally one for 1:500.000: https://drive.google.com/open?id=0B0Id8TV87m2qZ1kzd0FPOE5XQ3c And the issue is getting more obvious. All the tiles are generated just fine in all other directions, i.e. north, south, west. Does somebody maybe have an idea which would point me in the right direction? Thanks & best regards Torsten -Ursprüngliche Nachricht- Von: geoserver-users-requ...@lists.sourceforge.net [mailto:geoserver-users-requ...@lists.sourceforge.net] Gesendet: Dienstag, 31. Januar 2017 09:33 An: geoserver-users@lists.sourceforge.net Betreff: Geoserver-users Digest, Vol 128, Issue 48 Send Geoserver-users mailing list submissions to geoserver-users@lists.sourceforge.net To subscribe or unsubscribe via the World Wide Web, visit https://lists.sourceforge.net/lists/listinfo/geoserver-users or, via email, send a message with subject or body 'help' to geoserver-users-requ...@lists.sourceforge.net You can reach the person managing the list at geoserver-users-ow...@lists.sourceforge.net When replying, please edit your Subject line so it is more specific than "Re: Contents of Geoserver-users digest..." Today's Topics: 1. Caching beyond the bounds of a UTM Zone (torsten.d...@t-systems.com) 2. Re: Caching beyond the bounds of a UTM Zone (Julian Hollingbery) -- Message: 1 Date: Tue, 31 Jan 2017 07:21:10 + From: Subject: [Geoserver-users] Caching beyond the bounds of a UTM Zone To: Message-ID: <1331e1d0258149928f3f67f60bf21...@he105660.emea1.cds.t-internal.com> Content-Type: text/plain; charset="us-ascii" Dear List, I posted my question already on the GeoWebCache-mai
