Re: [postgis-users] Using PosGIS in one dimension
Hi Mike! -á. (snip) In fact, do you know about the reverse process (loading to numpy arrays)? It seems that Psycopg2 cannot do it but there is a project tackling that problem --- I don't know if this could eventually be incorporated into the mainstream driver (http://code.google.com/p/pgnumpy/ ). I've never heard of the package until now, but I'll check it out. There appears to be a sizable niche of presumably scientists that require linkage beteen numpy and postgres. Just loading directly with arrays without intermediate list construction would be the killer feature. Ironically, no matter how fast the underlying RDBMS gets, the DBAPI interface is the bottleneck in Python, and this is one of the major reasons why people go for HDF5-based solutions. This message summarizes what is happening: http://mail.scipy.org/pipermail/numpy-discussion/2007-July/028601.html and this one gives some figures: http://mail.scipy.org/pipermail/numpy-discussion/2006-November/024732.html (snip) That is what I was hoping for (only with less overhead from the 2nd, unused coordinate). Is it is possible to build the LINESTRING from the interval type on the fly for these operations (so as to keep the in-disk representation compact and also supportive of the exclude constraint)? I think you mean using an aggregate to combine many intervals from several rows into one. I don't see any aggregate functions for the range type, and as I mentioned, it wouldn't know how to express the union of two ranges that don't touch (i.e., a discontinuous range, which would require some MULTI* structure). PostGIS has several aggregates, like ST_Union and ST_MemUnion, all are pretty reliable and fast. Not sure about the exclude constraint, but I think it is based on the GiST index. Well, I think what I meant is something else (much simpler): can I store ranges and at the time of query cast them onto e.g. LINESTRINGS so as to be able to use the power of MULTI* in PostGIS (but in the future use constructs specific for ranges, when they get implemented)? Then what I need is actually a bit different: not to combine (merge) intervals in the same table, but to select only those intervals from table A that are contained in (intervals of table B, C and D). As an example imagine a database of playing chess, A is intervals of 'piece is in the air' and I want to get all such intervals contained in B 'player is looking at opponent' and C 'player has his jacket on' (assume he puts it on and off at random times during the game, responding to air conditioning). I know I am bad at examples, but I try my best not to invoke my (very specialized) example from electrophysiology. Do you advise to store linestrings instead of building them on the fly? Thanks, PS. by the way, I am sorry for the misspelling of PostGIS on the Subject, it was a typo. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
Hi Mike! Thank you. Your tip about loading numpy data into PostgreSQL will be key for my application ( http://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2 ) In fact, do you know about the reverse process (loading to numpy arrays)? It seems that Psycopg2 cannot do it but there is a project tackling that problem --- I don't know if this could eventually be incorporated into the mainstream driver (http://code.google.com/p/pgnumpy/ ). (snip) The range type has a whole host of set-style functions and operators, like union, difference, intersection, etc.: http://www.postgresql.org/docs/devel/static/functions-range.html One limitation of the range type that I came across is that there is no MULTI* equivalent. So you can have a continuous range of [4,10), but you can't punch a hole (6,7] within it (i.e., difference). To me, this difference yielding multi issue is conceptualized as: [4,10) - (6,7] = [4,6] _gap_ (7,10) .. can't work (throws an error), since there is no way to express a single range with a discontinuity. But with that thought, you could turn PostGIS into a similar 1D tool, using the MULTILINESTRING, using the x coordinate as your dimension, and ignoring all y coordinates. The above problem is: SELECT ST_AsText(ST_Difference( 'LINESTRING(4 0, 10 0)', 'LINESTRING(6 0, 7 0)')); st_astext --- MULTILINESTRING((4 0,6 0),(7 0,10 0)) (1 row) That is what I was hoping for (only with less overhead from the 2nd, unused coordinate). Is it is possible to build the LINESTRING from the interval type on the fly for these operations (so as to keep the in-disk representation compact and also supportive of the exclude constraint)? Thanks, Álvaro. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
On 27 April 2012 00:05, Alvaro Tejero Cantero alv...@minin.es wrote: Hi Mike! Thank you. Your tip about loading numpy data into PostgreSQL will be key for my application (http://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2) Great it's helpful! It probably deserves a better publishing platform than SO though. In fact, do you know about the reverse process (loading to numpy arrays)? It seems that Psycopg2 cannot do it but there is a project tackling that problem --- I don't know if this could eventually be incorporated into the mainstream driver (http://code.google.com/p/pgnumpy/ ). I've never heard of the package until now, but I'll check it out. There appears to be a sizable niche of presumably scientists that require linkage beteen numpy and postgres. (snip) That is what I was hoping for (only with less overhead from the 2nd, unused coordinate). Is it is possible to build the LINESTRING from the interval type on the fly for these operations (so as to keep the in-disk representation compact and also supportive of the exclude constraint)? I think you mean using an aggregate to combine many intervals from several rows into one. I don't see any aggregate functions for the range type, and as I mentioned, it wouldn't know how to express the union of two ranges that don't touch (i.e., a discontinuous range, which would require some MULTI* structure). PostGIS has several aggregates, like ST_Union and ST_MemUnion, all are pretty reliable and fast. Not sure about the exclude constraint, but I think it is based on the GiST index. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Using PosGIS in one dimension
Hello, I am interested in using some of the spatial operators to implement the interval algebra operations [1] in one dimension. My application is tagging of intervals in neurophysiological recordings, a little bit similar to what people do in genomics. I would like to know if it is possible, and whether PostGIS is also optimized for this scenario. A typical query is to retrieve all intervals of type A that are enclosed in intervals of type B. A few quick queries didn't return anything, and what I found about the INTERVAL datatype in Postgres[2] didn't fit our scenario so well, because they are using DATETIMEs and we need to use integer timestamps (4-byte integers are enough), as we are sampling time at 2Hz. Any comments are appreciated. Cheers, -á. [1] http://en.wikipedia.org/wiki/Allen's_interval_algebra [2] http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
If you follow PostgreSQL development, you can see that 9.2 is bringing a whole suite of range data types for dealing with one dimensional intervals with the same expressivity as PostGIS. Not there yet, but coming soon On Wed, Apr 25, 2012 at 9:52 AM, Alvaro Tejero Cantero alv...@minin.eswrote: Hello, I am interested in using some of the spatial operators to implement the interval algebra operations [1] in one dimension. My application is tagging of intervals in neurophysiological recordings, a little bit similar to what people do in genomics. I would like to know if it is possible, and whether PostGIS is also optimized for this scenario. A typical query is to retrieve all intervals of type A that are enclosed in intervals of type B. A few quick queries didn't return anything, and what I found about the INTERVAL datatype in Postgres[2] didn't fit our scenario so well, because they are using DATETIMEs and we need to use integer timestamps (4-byte integers are enough), as we are sampling time at 2Hz. Any comments are appreciated. Cheers, -á. [1] http://en.wikipedia.org/wiki/Allen's_interval_algebra [2] http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- David William Bitner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
On 26 April 2012 07:41, David William Bitner bit...@gyttja.org wrote: If you follow PostgreSQL development, you can see that 9.2 is bringing a whole suite of range data types for dealing with one dimensional intervals with the same expressivity as PostGIS. Not there yet, but coming soon Yup, I'll certainly agree with David's suggestion of the new range type for 9.2. You can define integer ranges using int4range. The range type shares some of the same DB technology as PostGIS, such as GiST indices, so it is sort-of a 1D version of PostGIS. A really good article describing the upcoming range type is at: http://www.depesz.com/2011/11/07/waiting-for-9-2-range-data-types/ -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
David, Mike, thank you very much for this spot-on information! I have two follow-up questions: 1/ is there an estimated release date for pg 9.2? (in its stead: are Postgres pre-release versions usually 'stable enough' - I am in an explorative setting, rock-solid stability is not yet needed). 2/ do these functions work with sets of intervals? I found an older post about an extension that seems to care for that http://scottrbailey.wordpress.com/2009/10/06/timespan_sets/ and I am concerned about how to do that efficiently (i.e. without a double loop on A intervals and B intervals). Thanks again, -á. On Wed, Apr 25, 2012 at 22:43, Mike Toews mwto...@gmail.com wrote: On 26 April 2012 07:41, David William Bitner bit...@gyttja.org wrote: If you follow PostgreSQL development, you can see that 9.2 is bringing a whole suite of range data types for dealing with one dimensional intervals with the same expressivity as PostGIS. Not there yet, but coming soon Yup, I'll certainly agree with David's suggestion of the new range type for 9.2. You can define integer ranges using int4range. The range type shares some of the same DB technology as PostGIS, such as GiST indices, so it is sort-of a 1D version of PostGIS. A really good article describing the upcoming range type is at: http://www.depesz.com/2011/11/07/waiting-for-9-2-range-data-types/ -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using PosGIS in one dimension
On 26 April 2012 09:54, Alvaro Tejero Cantero alv...@minin.es wrote: David, Mike, thank you very much for this spot-on information! I have two follow-up questions: 1/ is there an estimated release date for pg 9.2? (in its stead: are Postgres pre-release versions usually 'stable enough' - I am in an explorative setting, rock-solid stability is not yet needed). Here is the roadmap: http://www.postgresql.org/developer/roadmap/ It looks like beta releases start soon, followed by final in Q3 of 2012. As the range type is new, I'm sure the postgres developers would love to have some beta testers. I had a play with the range type several months ago, and I found it functional. 2/ do these functions work with sets of intervals? I found an older post about an extension that seems to care for that http://scottrbailey.wordpress.com/2009/10/06/timespan_sets/ and I am concerned about how to do that efficiently (i.e. without a double loop on A intervals and B intervals). The range type has a whole host of set-style functions and operators, like union, difference, intersection, etc.: http://www.postgresql.org/docs/devel/static/functions-range.html One limitation of the range type that I came across is that there is no MULTI* equivalent. So you can have a continuous range of [4,10), but you can't punch a hole (6,7] within it (i.e., difference). To me, this difference yielding multi issue is conceptualized as: [4,10) - (6,7] = [4,6] _gap_ (7,10) .. can't work (throws an error), since there is no way to express a single range with a discontinuity. But with that thought, you could turn PostGIS into a similar 1D tool, using the MULTILINESTRING, using the x coordinate as your dimension, and ignoring all y coordinates. The above problem is: SELECT ST_AsText(ST_Difference( 'LINESTRING(4 0, 10 0)', 'LINESTRING(6 0, 7 0)')); st_astext --- MULTILINESTRING((4 0,6 0),(7 0,10 0)) (1 row) -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
