Saving query layers
Etienne Bellemare
I'd like to save layers generated from queries. I've been using OpenJump lately to query PostGIS, but I'm unable to save a project and keep the layers. Is there a way I could save the queries as layers? I want the layer to stay dynamic (i.e. build from the database, not from a previous saved file) as the data is updated in real time. So when opening the project OJ would simply reexecute my query. This is what ArcMap is doing for instance. I don't know for others like QGIS.
On a different note, I'd like to propose a refresh button on the query layers, so instead of generating a second layer to get updated (actual situation), OJ would simply refresh the layer or replace it with a new one containing the exact same specifications.
Thanks,
Etienne
Fred Lehodey
it would be fantastic !
+1 ;)
Fred
Michaël Michaud
How do you query your database ?
If you query it with Open > Database, you get a "dynamic" query.
Query is re-run each time you pan or zoom (be careful, data which is not visible does not exist for OpenJUMP)
If you save your project (save project as), you save the query with the project.
If you re-open the project, you re-run the query...
The other way is Layer > Run SQL Query.
This is not dynamic at all. Result is loaded in-memory, then, OpenJUMP is deconnected.
Making this query dynamic is potentially difficult, and I wouldn't like it (I wouldn't like the query to run after each pan/zoom)
You can run the query as often as you want but it will create a new layer each time. If what you want is a refresh button to query the server again and to replace existing layer instead of adding a new one, I undersand and can write a feature request.
Please tell me if I understood your requests or if I missed something
Michaël
Fred Lehodey
In my case, I was thinking about the second way: Layer > Run SQL Query.
Like this:
1 - the query layer is saved with the project. (just sql and connection...)
2 - that reload in-memory happens when opening the project.
3 - There a button to refresh that query when we want...
Simple to say.... but...
Regards,
Fred
Michaël Michaud
Hi,
In my case, I was thinking about the second way: Layer > Run SQL Query.
Like this:
1 - the query layer is saved with the project. (just sql and connection...)
2 - that reload in-memory happens when opening the project.
3 - There a button to refresh that query when we want...
Also it would be a way to save queries, which is a bit painful currently.
The - is that my todo list grows up quite fast these days
The + is that I use RunSQL query about dayly, and I'm quite interested in adding this feature.
Will you or Etienne add a Feature request for that ?
https://sourceforge.net/tracker/?group_id=118054&atid=679909
Michaël
Fred Lehodey
Etienne Bellemare
In my case, I was thinking about the second way: Layer > Run SQL Query.
Like this:
1 - the query layer is saved with the project. (just sql and connection...)
2 - that reload in-memory happens when opening the project.
3 - There a button to refresh that query when we want...
Great to see how efficiently that request was handled !
Cheers,
Etienne
Michaël Michaud
I have implemented feature request 3206932.
It is not completely finished but it is ready for test / feedback.
You can download nighly builds at
http://jump-pilot.sourceforge.net/download/nightlybuild/
Points 1 and 2 (described hereafter by Fred) are implemented as it is described
3 is accessible from a right-click on layer names (submenu Datastore)
Some things to know
- if you right clic on the layer and choose "Layer properties" you now get
Datatype : DataStoreQueryDataSource
for a layer coming from Run SQL Query
- if you copy/paste the layer into a new layer, the new layer is not connected to the datastore (don't know if it's good or bad yet)
- if you modify the layer, save the project and reload it, all the modifications are lost (reload will re-run original query)
to keep your modification, save the modified layer as a shapefile or jml
Use of the fence or view button : ex. "select asbinary(geom) from mytable where geom && ${fence:-1}"
- what is recorded in the project is ${fence:-1}, not the actual geometry of the fence
- as the fence will not be recorded in the project, opening the project next time will load an empty layer
you still can create a new fence and reload the layer to get the data (see below)
Refresh (right click the layer name, then Datastore > Refresh the query)
- the query is run again, the dataset is replaced
- with a parametric query (using fence), you can easily load data here and there, just by moving the fence, and refreshing
Todo
- I'd like to add a plugin to load back the SQL query associated with a layer into the Run SQL Query dialog box, so that one can easily check what the query was and modify it
Regards,
Michaël
Jukka Rahkonen
The new nightly build does not start for me on Windows XP and Java 1.6.
Error is:
ava.lang.ExceptionInInitializerError
at
org.openjump.OpenJumpConfiguration.loadOpenJumpPlugIns(OpenJumpConfiguration.java:428)
at
com.vividsolutions.jump.workbench.JUMPConfiguration.setup(JUMPConfiguration.java:371)
at
com.vividsolutions.jump.workbench.JUMPWorkbench.main(JUMPWorkbench.java:353)
at
com.vividsolutions.jump.workbench.JUMPWorkbench.main(JUMPWorkbench.java:319)
Caused by: java.lang.NullPointerException
at javax.swing.ImageIcon.<init>(Unknown Source)
at org.openjump.core.ui.images.IconLoader.icon(IconLoader.java:44)
at
org.openjump.core.ui.plugin.datastore.RefreshDataStoreQueryPlugIn.<clinit>(RefreshDataStoreQueryPlugIn.java:35)
... 4 more
-Jukka Rahkonen-
Micha�l Michaud kirjoitti:
> Micha�l
>
>
> Le 13/03/2011 15:35, Etienne Bellemare a �crit :
Fred Lehodey
I have no problem to start OJ (1.4.1a) on Windows XP and Java 1.6
But... I can´t see new features added by Michael. Or I am doing something wrong... ??
Regards,
Fred
Hi,
The new nightly build does not start for me on Windows XP and Java 1.6.
Error is:
ava.lang.ExceptionInInitializerError
at
org.openjump.OpenJumpConfiguration.loadOpenJumpPlugIns(OpenJumpConfiguration.java:428)
at
com.vividsolutions.jump.workbench.JUMPConfiguration.setup(JUMPConfiguration.java:371)
at
com.vividsolutions.jump.workbench.JUMPWorkbench.main(JUMPWorkbench.java:353)
at
com.vividsolutions.jump.workbench.JUMPWorkbench.main(JUMPWorkbench.java:319)
Caused by: java.lang.NullPointerException
at javax.swing.ImageIcon.<init>(Unknown Source)
at org.openjump.core.ui.images.IconLoader.icon(IconLoader.java:44)
at
org.openjump.core.ui.plugin.datastore.RefreshDataStoreQueryPlugIn.<clinit>(RefreshDataStoreQueryPlugIn.java:35)
... 4 more
-Jukka Rahkonen-
Michaël Michaud kirjoitti:
> Michaël
Michaël Michaud
You're right Jukka, version from 21/03/2011 is buggy (forget to commit an Icon present on my local machine)
Fred, did you refresh you nightly build page to get the very last version,
Anyway, I'll make a test tomorrow against the new NB version before I come back to you,
Sorry for the noise,
Thanks
Michaël
Jukka Rahkonen
I tested these functionalities with new nightly build and everything
worked as you described against PostGIS 9.0. Great features which make
OpenJUMP even more powerful PostGIS query tool.
-Jukka Rahkonen-
Micha�l Michaud kirjoitti:
> Micha�l
>
>
> Le 13/03/2011 15:35, Etienne Bellemare a �crit :
Jukka Rahkonen
I tested these functionalities with new nightly build and everything
worked as you described against PostGIS 9.0. Great features which make
OpenJUMP even more powerful PostGIS query tool.
-Jukka Rahkonen-
Micha�l Michaud kirjoitti:
> Micha�l
>
>
> Le 13/03/2011 15:35, Etienne Bellemare a �crit :
Fred Lehodey
(sorry, as you said Michael, I was testing an older nightlybuild version :/)
I tested now, and it is just fantastic...
And with "view" and "fence", these functionalities are incredibles
I need to test more, but here just some notes:
A - the "edit datastore query" create a new layer... may be we can have here 2 options here: (??)
1 - one create a new (like now, but the name would be something like "use datastore query")
2 - one replace the actual layer (and keep the name "edit datab...")... fine to test queries without create layers....
B - about "view" and "fence":
1 - using [...WHERE the_geom && ${view:-1}] I have an error from PosGIS: operation on 2 differents SRID
I need to do that : [...WHERE the_geom && st_setsrid(${view:-1},27492)]
or, for example, if not interested just in Bbox [... WHERE ST_within(the_geom,st_setsrid(${view:-1},27492))]
2 - with "fence" no need to use ST_SetSRID() !
So, I don't understand why "fence" is able to know my srid but "view" is not..... :) (or I am doing something wrong?)
Nice work Michael !
Cheers,
Fred.
Hi,
I tested these functionalities with new nightly build and everything
worked as you described against PostGIS 9.0. Great features which make
OpenJUMP even more powerful PostGIS query tool.
-Jukka Rahkonen-
Michaël Michaud kirjoitti:
> Michaël
Etienne Bellemare
I also agree with Fred on A & B.
And must add C, that this is opening a whole world of opportunities:
1 - like refreshing on pan or zoom out,
2 - temporal refreshing, when dealing with live data (I'm using OpenJUMP to monitor a process, so if it could refresh every let's say 5 minutes)
Cheers,
Etienne
Michaël Michaud
Thanks for feedback and encouragments,
Here are some answers to your suggestions :
A - I think I'll try to do this (as a plugin option or as a new plugin)
B - I get an error in both cases (fence and view) where fence/view srid and server data srid do not fit
Note that ${view:-1} means view using srid -1
You should be able to do [...WHERE the_geom && ${view:27492}] instead of [...WHERE the_geom && st_setsrid(${view:-1},27492)]
Let me know if it works, my data mostly use the undefined -1 SRID
C.1 - refreshing on pan and zoom has pro and cons. I often use long queries taking time to refresh...
Let le know why you don't use Open > Datastore option to get more dynamic layers.
This way, you can get a table, add a where condition and keep the layer in sync with the server (on the other hand, you can't do complex queries).
C.2 - interesting alternative. Not very easy to implement (needs to manage a timer in each layer).
Note that to ease layers updating, I made it possible to refresh several query-based layer at a time.
Regards,
Michaël
Jukka Rahkonen
I believe I've found one possible bug. The feature count limit given
through the text box is not saved into the project file. It is hard to
detect because somehow OpenJUMP keeps the value that was fed into a box in
memory even if you close the project but you can see it by closing and
restarting OpenJUMP before opening the project with saved query layers.
This has a deadly effect if the layer comes from some multimillion row
table. OJ reads the table until all the memory is used. Only way to
re-open the saved project I discovered was to hand-edit the project file
and add some reasonable limit into the query.
It would be a good habit to use always a feature count limit with SQL
query layers and especially if the automatic refresh after zooming will be
implemented. Perhaps it would even be good to have some max value of 50000
- 100000 as a default.
Automatic refresh after panning or zooming gives exciting views. If
combined with the live-GPS plugin OpenJUMP could be made to a very nice
featured vector based moving map application. Layers with free SQL queries
could be used to select, for example, just a hundred biggest lakes (ORDER
BY area limit 100;) that fall inside the view area which would make
rendering and refreshing much faster at small scales.
If OJ would refresh views automatically after panning (naturally user
should select this option for each layer) then perhaps the time based
refresh could be tweaked by doing timed one pixel pan after n seconds? Or
would even recentering work?
Now there are buttons for view and fence. How about a third one for the
centre point of the view? It could be used for "distance within" queries
or another fascinating possibility, "n closest features".
-Jukka Rahkonen-
Micha�l Michaud kirjoitti:
> Hi all,
>
> Thanks for feedback and encouragments,
>
> Here are some answers to your suggestions :
>
> A - I think I'll try to do this (as a plugin option or as a new plugin)
>
> B - I get an error in both cases (fence and view) where fence/view srid
> and server data srid do not fit
> Note that ${view:-1} means view using srid -1
> You should be able to do [...WHERE the_geom && ${view:27492}] instead of
> [...WHERE the_geom && st_setsrid(${view:-1},27492)]
> Let me know if it works, my data mostly use the undefined -1 SRID
>
> C.1 - refreshing on pan and zoom has pro and cons. I often use long
> queries taking time to refresh...
> Let le know why you don't use Open > Datastore option to get more
> dynamic layers.
> This way, you can get a table, add a where condition and keep the layer
> in sync with the server (on the other hand, you can't do complex queries).
>
> C.2 - interesting alternative. Not very easy to implement (needs to
> manage a timer in each layer).
> Note that to ease layers updating, I made it possible to refresh several
> query-based layer at a time.
>
> Regards,
>
> Micha�l
>
> Le 23/03/2011 19:17, Etienne Bellemare a �crit :
Fred Lehodey
I agree, who uses SQL Query knows about LIMIT clause....
Not sure, but would be more useful to replace that text box to "name of query", avoiding to rename queries in layers panel. :)
For the center point of the view, you already can do this:
select asbinary(the_geom), * FROM my_tab
WHERE ST_dwithin(the_geom,ST_Centroid(${view:mySrid}),myDistance)
Works fine for me...
Fred.
-Jukka Rahkonen-
Michaël Michaud kirjoitti:
> Hi all,
>
> Thanks for feedback and encouragments,
>
> Here are some answers to your suggestions :
>
> A - I think I'll try to do this (as a plugin option or as a new plugin)
>
> B - I get an error in both cases (fence and view) where fence/view srid
> and server data srid do not fit
> Note that ${view:-1} means view using srid -1
> You should be able to do [...WHERE the_geom && ${view:27492}] instead of
> [...WHERE the_geom && st_setsrid(${view:-1},27492)]
> Let me know if it works, my data mostly use the undefined -1 SRID
>
> C.1 - refreshing on pan and zoom has pro and cons. I often use long
> queries taking time to refresh...
> Let le know why you don't use Open > Datastore option to get more
> dynamic layers.
> This way, you can get a table, add a where condition and keep the layer
> in sync with the server (on the other hand, you can't do complex queries).
>
> C.2 - interesting alternative. Not very easy to implement (needs to
> manage a timer in each layer).
> Note that to ease layers updating, I made it possible to refresh several
> query-based layer at a time.
>
> Regards,
>
> Michaël
Fred Lehodey
sorry, here the same query with limit feature based on distance:
select asbinary(the_geom), * FROM my_tab
ORDER BY ST_Distance(the_geom,ST_Centroid(${view:mySRID}))
LIMIT myLimitNbFeatures
Fred.
Jukka Rahkonen
Beautiful. You folks still stuck with shapefiles, look at this!
PostGIS is a bit hard to install and administrate. On the other hand, we
have two nice Spatialite extensions. I wonder if either of them could be
integrated into OpenJUMP so we would have a native support for Spatialite.
QGis supports Spatialite but our plugins are much better because they
support queries.
-Jukka-
>>> Micha�l Michaud kirjoitti:
>>> > Micha�l
>>> >
>>> > Le 23/03/2011 19:17, Etienne Bellemare a �crit :
Michaël Michaud
You're right, I forgot to mention I did not persist the feature count limit,
The reason is I never use it and I'm not sure it is useful.
I agree with Fred that SQL users generally know how to use the LIMIT clause in SQL
Also the limit is both application and hardware dependant
I also agree with Fred that a textField to enter the layer name could be more useful.
I'll try to get more feedback about this point before making decision.
But you're right, currently, it appears as a bug, and must find a solution before 1.4.1 release.
About automatic refreshing, can you fill a feature request.
I think it can definitely be useful... in some cases. So an option could be added, but I'll not put that request very high in my priorities.
Center button : IMHO, the feature will not be very useful because you rarely want to see less features than the ones included in the view bounding box, and because you can always do it in SQL as shown by Fred.
On the other hand, this one is quite easy to implement, so if several users find it useful, I can add it.
Anyway, thanks very much for feedback. It is very useful for developpers get comments,
Michaël
Jukka Rahkonen
If there is a box where you can enter max feature count then some people
will use it. I think there are two as good alternatives, either remove the
box or make it to work in a logical way.
Text box for naming the layer would be useful.
Center button or taking coordinates from a clicked point would help also
in selecting more features than included inside the map view. For example
"Select addresses closer than 80 km from this smoking nuclear plant". Sure
it is always possible to do it with SQL but we do have users who are
frightened with things like
select asbinary(the_geom), * FROM my_tab
WHERE ST_dwithin(the_geom,ST_Centroid(${view:mySrid}),myDistance)
Perhaps some day we could have a spatial query wizard for PostGIS.
Something like we already have for Spatialite, see last images of the
tutorial
http://sourceforge.net/apps/mediawiki/jump-pilot/index.php?title=OpenJUMP_with_SpatialLite
-Jukka Rahkonen-
Micha�l Michaud kirjoitti:
> Hi Jukka, Fred,
>
> You're right, I forgot to mention I did not persist the feature count
> limit,
> The reason is I never use it and I'm not sure it is useful.
> I agree with Fred that SQL users generally know how to use the LIMIT
> clause in SQL
> Also the limit is both application and hardware dependant
>
> I also agree with Fred that a textField to enter the layer name could be
> more useful.
> I'll try to get more feedback about this point before making decision.
>
> But you're right, currently, it appears as a bug, and must find a
> solution before 1.4.1 release.
>
> About automatic refreshing, can you fill a feature request.
> I think it can definitely be useful... in some cases. So an option could
> be added, but I'll not put that request very high in my priorities.
>
> Center button : IMHO, the feature will not be very useful because you
> rarely want to see less features than the ones included in the view
> bounding box, and because you can always do it in SQL as shown by Fred.
> On the other hand, this one is quite easy to implement, so if several
> users find it useful, I can add it.
>
> Anyway, thanks very much for feedback. It is very useful for developpers
> get comments,
>
> Micha�l
>
>
>
>
> Le 24/03/2011 13:19, Fred Lehodey a �crit :
>> Hi Jukka,
>>
>> I agree, who uses SQL Query knows about LIMIT clause....
>> Not sure, but would be more useful to replace that text box to "name
>> of query", avoiding to rename queries in layers panel. :)
>>
>> For the center point of the view, you already can do this:
>> select asbinary(the_geom), * FROM my_tab
>> WHERE ST_dwithin(the_geom,ST_Centroid(${view:mySrid}),myDistance)
>>
>> Works fine for me...
>>
>> Fred.
>>
>>
>> On Thu, Mar 24, 2011 at 11:54 AM, Jukka Rahkonen
>> <jukka.r...@latuviitta.fi <mailto:jukka.r...@latuviitta.fi>>
>> Micha�l Michaud kirjoitti:
>> > Micha�l
>> >
>> > Le 23/03/2011 19:17, Etienne Bellemare a �crit :
Etienne Bellemare
However, I must say that the ability to save queries proved to be a very useful feature.
Thanks again,
Etienne
Hi,
If there is a box where you can enter max feature count then some people
will use it. I think there are two as good alternatives, either remove the
box or make it to work in a logical way.
Text box for naming the layer would be useful.
Center button or taking coordinates from a clicked point would help also
in selecting more features than included inside the map view. For example
"Select addresses closer than 80 km from this smoking nuclear plant". Sure
it is always possible to do it with SQL but we do have users who are
frightened with things like
select asbinary(the_geom), * FROM my_tabPerhaps some day we could have a spatial query wizard for PostGIS.
WHERE ST_dwithin(the_geom,ST_Centroid(${view:mySrid}),myDistance)
Something like we already have for Spatialite, see last images of the
tutorial
http://sourceforge.net/apps/mediawiki/jump-pilot/index.php?title=OpenJUMP_with_SpatialLite
-Jukka Rahkonen-
Michaël Michaud kirjoitti:
> Hi Jukka, Fred,
>
> You're right, I forgot to mention I did not persist the feature count
> limit,
> The reason is I never use it and I'm not sure it is useful.
> I agree with Fred that SQL users generally know how to use the LIMIT
> clause in SQL
> Also the limit is both application and hardware dependant
>
> I also agree with Fred that a textField to enter the layer name could be
> more useful.
> I'll try to get more feedback about this point before making decision.
>
> But you're right, currently, it appears as a bug, and must find a
> solution before 1.4.1 release.
>
> About automatic refreshing, can you fill a feature request.
> I think it can definitely be useful... in some cases. So an option could
> be added, but I'll not put that request very high in my priorities.
>
> Center button : IMHO, the feature will not be very useful because you
> rarely want to see less features than the ones included in the view
> bounding box, and because you can always do it in SQL as shown by Fred.
> On the other hand, this one is quite easy to implement, so if several
> users find it useful, I can add it.
>
> Anyway, thanks very much for feedback. It is very useful for developpers
> get comments,
>
> Michaël
>> <jukka.r...@latuviitta.fi <mailto:jukka.r...@latuviitta.fi>>
>> Michaël Michaud kirjoitti:
>> > Michaël
Michaël Michaud
I don't know why features of refreshed layers are tagged as modified. I filled a bug report and will try to fix it before next release.
Michaël
Michaël Michaud
A strange behaviour I've noticed is that when you open a project, refresh a layer and close the project, Open Jumps warns you that you've made changes (which is not really true). That might mix up some users as even if you save the project, the warning doesn't stop.
To be tested with Larry's patch (who made SELECT the_geom FROM.... be a valid syntax for run datastore query)
Michaël