Re: [Geotools-devel] imagemosaic-jdbc pgraster time domain support
Hi Lucas, contribution rules are listed here: https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md About discussing it, I cannot help personally, but I've cc'ed the module maintainer. Cheers Andrea On Wed, Jun 28, 2017 at 11:26 AM, Lucas van Oosterhout < l.van.oosterh...@mmguide.nl> wrote: > Hi, > > > > I would like to further discuss the possibility of adding time domain support > to imagemosaic-jdbc for pgraster. This was proposed by Frank Gevearts to the > mailing list. (https://sourceforge.net/p/geotools/mailman/message/35220963/) > > > > We have been working with the code we received from Frank Gevaerts and we > would like to share it with the community. > > We are willing, if time allows it from our side, to improve it to a degree > where it can be included into the project. > > At the moment there are no extra tests or documentation about the feature, > and there is still the issue of what to return when overlapping (in time) > data is found. > > > > I haven’t contributed to an open source project before, as such I am a bit at > a loss as how to continue and share the code. I have included a patch which > can be applied to master. > > > > Yours, > > > > Lucas van Oosterhout > > > > Software Developer > > MMGuide > > Utrecht, The Netherlands > > > > -- > Check out the vibrant tech community on one of the world's most > engaging tech sites, Slashdot.org! http://sdm.link/slashdot > ___ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > -- Regards, Andrea Aime == 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___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] imagemosaic-jdbc pgraster time domain support
Hi, I would like to further discuss the possibility of adding time domain support to imagemosaic-jdbc for pgraster. This was proposed by Frank Gevearts to the mailing list. (https://sourceforge.net/p/geotools/mailman/message/35220963/) We have been working with the code we received from Frank Gevaerts and we would like to share it with the community. We are willing, if time allows it from our side, to improve it to a degree where it can be included into the project. At the moment there are no extra tests or documentation about the feature, and there is still the issue of what to return when overlapping (in time) data is found. I haven’t contributed to an open source project before, as such I am a bit at a loss as how to continue and share the code. I have included a patch which can be applied to master. Yours, Lucas van Oosterhout Software Developer MMGuide Utrecht, The Netherlands added_option_for_timedimension_on_imagemosaicjdbc_rasters1.patch Description: added_option_for_timedimension_on_imagemosaicjdbc_rasters1.patch -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] imagemosaic-jdbc pgraster time domain support
Hi Frank Some answers: Oracle and DB2 have a community edition for testing (dont know about Microsoft). Unfortunately it is necessary to install all these products(using a virtual machine) . But let us put the focus on your arguments first. +1 for considering the fact that we have to integrate with GeoServer. About getting more tile versions for a point in time. There is no simple solution. It may be wrong if you use the first one, the last one, the average and so on. It is no good idea to implement "fuzzy" logic. There are 2 clean solutions: 1) Assure that you do not have overlapping time periods for your raster data. 2) Otherwise you need a second time dimension using the system current time stamp (from and to). The query has to be "Give me the tiles valid to a certain point in time stored in the system with physical timestamp xxx ". This design is often named as two dimensional history. About pointers to the SQL 2011 time support: https://en.wikipedia.org/wiki/SQL:2011 Please read carefully and you will see the different syntax and concepts. Another problem is that geotools/geoserver uses a period with a closed - closed interval whilst the SQL standard uses an closed-open interval. Example: 01-01-2106 - 01-01-2017 01-01-2107 - 01-01-2018 GeoServer includes 01-01-217, but the SQL Standard includes only 31-12-2016. If your database support SQL2011 you will get 2 versions for date 01-01-2017 because the produced query uses <= and >= As you can see, the whole thing is tricky, anyway I am interested in your arguments. Cheers Christian On Mon, Jul 18, 2016 at 12:12 PM, Frank Gevaerts wrote: > On Sat, Jul 16, 2016 at 10:07:42AM +0200, Christian Mueller wrote: > > Hi Frank > > > > First I want to thank you for the contribution. Time support is quite a > > challenge because it should be available to all supported databases > > including Oracle GeoRaster, Oracle native, DB2,. > > I agree it *should* work on all of those, but unless someone actually > has access to all of them, I don't think adding support for everything > at the same time is very realistic. As I see it, the best we can do is > try to ensure the shared code doesn't make assumptions that won't hold > on systems that aren't updated right away. > > I've started on pgraster because that's what we want to use here, and I > can definitely work on generic postgresql and generic jdbc. Mysql might > be possible (I can definitely install it, but I don't have much mysql > experience at all), but I don't have access to oracle or db2 servers. > > > Additionally the time support should work with GeoServer as described > here > > > http://docs.geoserver.org/latest/en/user/services/wms/time.html#specifying-a-time > > Yes. That works right now (it's what I use for testing), except possibly > for periodicity (I'll check and fix if needed). > > There is an open question though: what's supposed to be returned if more > than one raster tile from the database matches the request for a given > location? Latest data for each point? Only points for the latest timestamp? > Some sort of average? I assume "latest data for each point" makes the most > sense, but it's also the hardest to do. > I know my code right now doesn't really handle this at all (you'll get > data, but which particular bit isn't deterministic). > > Only points for the latest timestamp seems fairly easy to handle, but > latest data for each point is tricky. There are several ways I've looked > at: > > * No multithreaded decoding or composing, so an ORDER BY time on the > database query will enforce "latest" or "earliest". This won't work for > averages, and dropping multithreading is a rather high price to pay. > * Have the database query do the filtering. For pgraster, as far as I can > see this implies having either consistent tiling for all data (i.e. > tiles for different times have exactly the same grid) or having the > query (slowly...) retile everything. For systems without in-database > raster systems I don't see a way to do it. > > > The next point is that some database engines (DB2,Oracle) have a built in > > time support which is slightly different to the time support provided by > > geotools. > > Do you have some pointers to documentation? > > Frank > > > Nevertheless I want to hear your opinion. > > > > Cheers > > Christian > > > > > > -- > Frank Gevaerts frank.gevae...@fks.be > fks bvba - Formal and Knowledge Systemshttp://www.fks.be/ > Schampbergstraat 32Tel: ++32-(0)11-21 49 11 > B-3511 KURINGEN-HASSELTFax: ++32-(0)11-22 04 19 > -- DI Christian Mueller MSc (GIS), MSc (IT-Security) OSS Open Source Solutions GmbH -- What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are
Re: [Geotools-devel] imagemosaic-jdbc pgraster time domain support
On Sat, Jul 16, 2016 at 10:07:42AM +0200, Christian Mueller wrote: > Hi Frank > > First I want to thank you for the contribution. Time support is quite a > challenge because it should be available to all supported databases > including Oracle GeoRaster, Oracle native, DB2,. I agree it *should* work on all of those, but unless someone actually has access to all of them, I don't think adding support for everything at the same time is very realistic. As I see it, the best we can do is try to ensure the shared code doesn't make assumptions that won't hold on systems that aren't updated right away. I've started on pgraster because that's what we want to use here, and I can definitely work on generic postgresql and generic jdbc. Mysql might be possible (I can definitely install it, but I don't have much mysql experience at all), but I don't have access to oracle or db2 servers. > Additionally the time support should work with GeoServer as described here > http://docs.geoserver.org/latest/en/user/services/wms/time.html#specifying-a-time Yes. That works right now (it's what I use for testing), except possibly for periodicity (I'll check and fix if needed). There is an open question though: what's supposed to be returned if more than one raster tile from the database matches the request for a given location? Latest data for each point? Only points for the latest timestamp? Some sort of average? I assume "latest data for each point" makes the most sense, but it's also the hardest to do. I know my code right now doesn't really handle this at all (you'll get data, but which particular bit isn't deterministic). Only points for the latest timestamp seems fairly easy to handle, but latest data for each point is tricky. There are several ways I've looked at: * No multithreaded decoding or composing, so an ORDER BY time on the database query will enforce "latest" or "earliest". This won't work for averages, and dropping multithreading is a rather high price to pay. * Have the database query do the filtering. For pgraster, as far as I can see this implies having either consistent tiling for all data (i.e. tiles for different times have exactly the same grid) or having the query (slowly...) retile everything. For systems without in-database raster systems I don't see a way to do it. > The next point is that some database engines (DB2,Oracle) have a built in > time support which is slightly different to the time support provided by > geotools. Do you have some pointers to documentation? Frank > Nevertheless I want to hear your opinion. > > Cheers > Christian > > -- Frank Gevaerts frank.gevae...@fks.be fks bvba - Formal and Knowledge Systemshttp://www.fks.be/ Schampbergstraat 32Tel: ++32-(0)11-21 49 11 B-3511 KURINGEN-HASSELTFax: ++32-(0)11-22 04 19 -- What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev ___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] imagemosaic-jdbc pgraster time domain support
Hi Frank First I want to thank you for the contribution. Time support is quite a challenge because it should be available to all supported databases including Oracle GeoRaster, Oracle native, DB2,. Additionally the time support should work with GeoServer as described here http://docs.geoserver.org/latest/en/user/services/wms/time.html#specifying-a-time The next point is that some database engines (DB2,Oracle) have a built in time support which is slightly different to the time support provided by geotools. Nevertheless I want to hear your opinion. Cheers Christian On Fri, Jul 15, 2016 at 11:09 PM, Jody Garnett wrote: > That is exciting, I checked and Christian Mueller is the module maintainer > for imagemosaic-jdbc so we kind of would like his input on the technical > direction you are taking (although it seems clear to me). > > Thank you for taking this on, and communicating clearly on the developers > list. > > -- > Jody Garnett > > On 14 July 2016 at 09:15, Frank Gevaerts wrote: > >> Hi, >> >> I've been working on adding time domain support to imagemosaic-jdbc, >> specifically >> for pgraster. The current state is that it seems to work, but it's really >> probably >> at a proof of concept stage. I haven't done anything about tests or >> documentation, >> yet, and apart from making sure things still build I haven't updated >> drivers other >> than pgraster. >> >> The current code is attached. At this point I'd mainly like to know if >> I'm on the >> right track, and I'd appreciate some ideas about what sort and level of >> testing is >> needed. >> >> To add time domain support, the element gets a new >> >> sub-element. If that element is present time domain support is enabled. >> The >> corresponding field needs to be of a type that's handled by jdbc as a >> Date, e.g. >> timestamp. >> >> >> >> >> >> >> I can make a pull request if it helps with reviewing. >> >> Frank >> >> -- >> Frank Gevaerts frank.gevae...@fks.be >> fks bvba - Formal and Knowledge Systemshttp://www.fks.be/ >> Schampbergstraat 32Tel: ++32-(0)11-21 49 11 >> B-3511 KURINGEN-HASSELTFax: ++32-(0)11-22 04 19 >> >> >> -- >> What NetFlow Analyzer can do for you? Monitors network bandwidth and >> traffic >> patterns at an interface-level. Reveals which users, apps, and protocols >> are >> consuming the most bandwidth. Provides multi-vendor support for NetFlow, >> J-Flow, sFlow and other flows. Make informed decisions using capacity >> planning >> reports.http://sdm.link/zohodev2dev >> ___ >> GeoTools-Devel mailing list >> GeoTools-Devel@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/geotools-devel >> >> > > > -- > What NetFlow Analyzer can do for you? Monitors network bandwidth and > traffic > patterns at an interface-level. Reveals which users, apps, and protocols > are > consuming the most bandwidth. Provides multi-vendor support for NetFlow, > J-Flow, sFlow and other flows. Make informed decisions using capacity > planning > reports.http://sdm.link/zohodev2dev > ___ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > -- DI Christian Mueller MSc (GIS), MSc (IT-Security) OSS Open Source Solutions GmbH -- What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] imagemosaic-jdbc pgraster time domain support
That is exciting, I checked and Christian Mueller is the module maintainer for imagemosaic-jdbc so we kind of would like his input on the technical direction you are taking (although it seems clear to me). Thank you for taking this on, and communicating clearly on the developers list. -- Jody Garnett On 14 July 2016 at 09:15, Frank Gevaerts wrote: > Hi, > > I've been working on adding time domain support to imagemosaic-jdbc, > specifically > for pgraster. The current state is that it seems to work, but it's really > probably > at a proof of concept stage. I haven't done anything about tests or > documentation, > yet, and apart from making sure things still build I haven't updated > drivers other > than pgraster. > > The current code is attached. At this point I'd mainly like to know if I'm > on the > right track, and I'd appreciate some ideas about what sort and level of > testing is > needed. > > To add time domain support, the element gets a new > > sub-element. If that element is present time domain support is enabled. The > corresponding field needs to be of a type that's handled by jdbc as a > Date, e.g. > timestamp. > > > > > > > I can make a pull request if it helps with reviewing. > > Frank > > -- > Frank Gevaerts frank.gevae...@fks.be > fks bvba - Formal and Knowledge Systemshttp://www.fks.be/ > Schampbergstraat 32Tel: ++32-(0)11-21 49 11 > B-3511 KURINGEN-HASSELTFax: ++32-(0)11-22 04 19 > > > -- > What NetFlow Analyzer can do for you? Monitors network bandwidth and > traffic > patterns at an interface-level. Reveals which users, apps, and protocols > are > consuming the most bandwidth. Provides multi-vendor support for NetFlow, > J-Flow, sFlow and other flows. Make informed decisions using capacity > planning > reports.http://sdm.link/zohodev2dev > ___ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > -- What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
[Geotools-devel] imagemosaic-jdbc pgraster time domain support
Hi, I've been working on adding time domain support to imagemosaic-jdbc, specifically for pgraster. The current state is that it seems to work, but it's really probably at a proof of concept stage. I haven't done anything about tests or documentation, yet, and apart from making sure things still build I haven't updated drivers other than pgraster. The current code is attached. At this point I'd mainly like to know if I'm on the right track, and I'd appreciate some ideas about what sort and level of testing is needed. To add time domain support, the element gets a new sub-element. If that element is present time domain support is enabled. The corresponding field needs to be of a type that's handled by jdbc as a Date, e.g. timestamp. I can make a pull request if it helps with reviewing. Frank -- Frank Gevaerts frank.gevae...@fks.be fks bvba - Formal and Knowledge Systemshttp://www.fks.be/ Schampbergstraat 32Tel: ++32-(0)11-21 49 11 B-3511 KURINGEN-HASSELTFax: ++32-(0)11-22 04 19 >From 4eade4c40c1918b4fab33fda04bfca2fd107279e Mon Sep 17 00:00:00 2001 From: Frank Gevaerts Date: Tue, 5 Jul 2016 14:04:04 +0200 Subject: [PATCH 3/5] PGRaster time domain support --- .../org/geotools/gce/imagemosaic/jdbc/Config.java | 8 +- .../imagemosaic/jdbc/ImageMosaicJDBCFormat.java| 2 +- .../imagemosaic/jdbc/ImageMosaicJDBCReader.java| 79 - .../geotools/gce/imagemosaic/jdbc/JDBCAccess.java | 9 +- .../gce/imagemosaic/jdbc/JDBCAccessBase.java | 15 +- .../imagemosaic/jdbc/custom/JDBCAccessCustom.java | 2 +- .../jdbc/custom/JDBCAccessOracleGeoRaster.java | 15 +- .../jdbc/custom/JDBCAccessPGRaster.java| 182 - .../jdbc/custom/JDBCAccessH2Custom.java| 14 +- 9 files changed, 313 insertions(+), 13 deletions(-) diff --git a/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/Config.java b/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/Config.java index b268632..4f7d110 100644 --- a/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/Config.java +++ b/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/Config.java @@ -80,6 +80,8 @@ public class Config { private String keyAttributeNameInTileTable; +private String timeAttributeNameInTileTable; + private String keyAttributeNameInSpatialTable; private String geomAttributeNameInSpatialTable; @@ -124,7 +126,6 @@ public class Config { private String jdbcAccessClassName; - protected Config() { } @@ -219,6 +220,7 @@ public class Config { if (tileTableElem!=null) { result.blobAttributeNameInTileTable = readNameString(tileTableElem, "blobAttributeName"); result.keyAttributeNameInTileTable = readNameString(tileTableElem, "keyAttributeName"); +result.timeAttributeNameInTileTable = readNameString(tileTableElem, "timeAttributeName"); } @@ -508,5 +510,7 @@ public class Config { return ignoreAxisOrder; } - +public String getTimeAttributeNameInTileTable() { +return timeAttributeNameInTileTable; +} } diff --git a/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCFormat.java b/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCFormat.java index f2a2121..2dcb0b6 100644 --- a/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCFormat.java +++ b/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCFormat.java @@ -156,7 +156,7 @@ public class ImageMosaicJDBCFormat extends AbstractGridFormat implements Format readParameters = new ParameterGroup( new DefaultParameterDescriptorGroup(mInfo, new GeneralParameterDescriptor[] { READ_GRIDGEOMETRY2D, -OUTPUT_TRANSPARENT_COLOR, BACKGROUND_COLOR })); +OUTPUT_TRANSPARENT_COLOR, BACKGROUND_COLOR, TIME })); // reading parameters writeParameters = null; diff --git a/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCReader.java b/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCReader.java index 8be5602..147d602 100644 --- a/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCReader.java +++ b/modules/plugin/imagemosaic-jdbc/src/main/java/org/geotools/gce/imagemosaic/jdbc/ImageMosaicJDBCReader.java @@ -25,8 +25,10 @@ import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.MalformedURLException; import java.net.URL; +import java.util.ArrayList; import java.util.Date; import java.util.HashSet; +import java.util