Re: [postgis-users] encryption of PostGIS databases
You can use the pgctrypto functions from contrib, but you might be better off securing the database and connections to it, rather than encrypting the data. Apart from the overhead of encrypting and decrypting the large volumes of data typically associated with geometry, I suspect your indexing would suffer. cheers Ben On 12/05/2011, at 8:30 PM, Malm Paul wrote: > Hi, > Is there a way to protect geographical data by encrypt the GIS db, if so that > is the impact on performance? > > Kind regards, > Paul > ___ > 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] encryption of PostGIS databases
Hi Paul, This is more of a PostgreSQL question, see: http://www.postgresql.org/docs/9.0/static/encryption-options.html You can require that all connections use SSL and make sure all users have strong passwords. -Mike On 13 May 2011 00:30, Malm Paul wrote: > Hi, > Is there a way to protect geographical data by encrypt the GIS db, if so > that is the impact on performance? > > Kind regards, > Paul > ___ > 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
[postgis-users] encryption of PostGIS databases
Hi, Is there a way to protect geographical data by encrypt the GIS db, if so that is the impact on performance? Kind regards, Paul ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Thu, May 12, 2011 at 10:35, Sandro Santilli wrote: > On Thu, May 12, 2011 at 10:24:28AM +0200, Magnus Hagander wrote: > >> Like I said - we want the underlying fix, but I do beleive we want the >> fix in PostGIS *as well*. > > We surely want the fix in ST_Translate, or whatever _produced_ the inf > coordinate. Yes, we want to fix that *as well*. > Not sure we want to refuse to accept inf coordinates though, > as long as ST_IsValid can catch them, but most importantly > as long as you can have them in your backups... Well, we should definitely refuse going into an infinte loop... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Fastest way to load an area of ways
2011/5/12 Hens,Markus M. > In my oppinion, the query is still slow. So my question to you is: Is this > the best way to cache such an area of ways or is there an faster alternative? What is "slow", in your opinion? Milliseconds? Minutes? Months? Have you done 'EXPLAIN ANALYZE' to see where the time is spent? -- T ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On 12/05/11 09:35, Sandro Santilli wrote: Like I said - we want the underlying fix, but I do beleive we want the fix in PostGIS *as well*. We surely want the fix in ST_Translate, or whatever _produced_ the inf coordinate. Not sure we want to refuse to accept inf coordinates though, as long as ST_IsValid can catch them, but most importantly as long as you can have them in your backups... Yes - the issue was that we were trying to get out of Frank what the correct behaviour should be to engineer a better fix. See http://postgis.refractions.net/pipermail/postgis-users/2011-March/029125.html for the relevant thread. If you could follow this up on the PROJ.4 list, that would be greatly appreciated. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Fastest way to load an area of ways
Hi all,
My name is Markus and I am doing my bachelor thesis on an OpenStreetMap Routing
topic.
Therefore I use a postGIS db to store the osm data. Actually I have a dump of
the data of Germany.
For my routing I cache areas. These areas I access like this:
SELECT id, version, tags, nodes FROM ways WHERE linestring &&
ST_SetSRID('BOX3D(9. 50.000, 9.005000 50.005)'::box3d,4326)
AND(
'highway=>motorway'::hstore <@
tags
OR
'highway=>motorway_link'::hstore <@ tags
OR 'highway=>trunk'::hstore <@
tags
OR
'highway=>trunk_link'::hstore <@ tags
OR 'highway=>primary'::hstore
<@ tags
OR
'highway=>primary_link'::hstore <@ tags
OR 'highway=>secondary'::hstore
<@ tags
OR
'highway=>secondary_link'::hstore <@ tags
OR 'highway=>tertiary'::hstore
<@ tags
OR
'highway=>unclassified'::hstore <@ tags
OR
'highway=>residential'::hstore <@ tags
OR
'highway=>living_street'::hstore <@ tags
);
The x and y of the box3d variate but every time have the same range of 0.005.
Linestring is the gist index. I did a Vacuum.
Table (ways) has 9040062 rows.
In my oppinion, the query is still slow. So my question to you is: Is this the
best way to cache such an area of ways or is there an faster alternative?
Best regards,
Markus Hens
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Thu, May 12, 2011 at 10:24:28AM +0200, Magnus Hagander wrote: > Like I said - we want the underlying fix, but I do beleive we want the > fix in PostGIS *as well*. We surely want the fix in ST_Translate, or whatever _produced_ the inf coordinate. Not sure we want to refuse to accept inf coordinates though, as long as ST_IsValid can catch them, but most importantly as long as you can have them in your backups... -- () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Thu, May 12, 2011 at 10:20, Sandro Santilli wrote: > On Thu, May 12, 2011 at 09:53:38AM +0200, Magnus Hagander wrote: >> On Tue, Mar 8, 2011 at 10:16, Magnus Hagander wrote: >> > On Mon, Feb 28, 2011 at 16:35, Mark Cave-Ayland >> > wrote: >> >> On 28/02/11 15:18, Magnus Hagander wrote: > >> >>> Running the following query locks up postgis completely (in >> >>> geos::algorithm::RobustDeterminant): >> >>> >> >>> SELECT st_intersects(somegeometry, >> >>> >> >>> '010320E61001000500737979F3DDCC2CC0F92154F9E7534540F07FF07F8F806E993F7E55C0304B29FFEA8554400634E8D1DD424540B5FEE6A37FCD4540737979F3DDCC2CC0F92154F9E7534540'::geometry) >> >>> >> >>> I believe this is because there are infinite values in that geometry: > > >> > Since nobody appears to be too interested in producing a quick fix in >> > geos, attached is a patch that puts in an *ugly* workaround in >> > PostGIS, that simply rejects the infinite values higher up in the >> > stack. > > Please see http://trac.osgeo.org/geos/ticket/357 > It was waiting for a testcase, as none helped reproducing > with 3.3.0rc1 so far. > I'll try with the data you give. It may need a second geometry to tango. Well, you can pick any geometry you want as the second one, and it'll lock up in PostGIS as well. Like I said - we want the underlying fix, but I do beleive we want the fix in PostGIS *as well*. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Thu, May 12, 2011 at 09:53:38AM +0200, Magnus Hagander wrote: > On Tue, Mar 8, 2011 at 10:16, Magnus Hagander wrote: > > On Mon, Feb 28, 2011 at 16:35, Mark Cave-Ayland > > wrote: > >> On 28/02/11 15:18, Magnus Hagander wrote: > >>> Running the following query locks up postgis completely (in > >>> geos::algorithm::RobustDeterminant): > >>> > >>> SELECT st_intersects(somegeometry, > >>> > >>> '010320E61001000500737979F3DDCC2CC0F92154F9E7534540F07FF07F8F806E993F7E55C0304B29FFEA8554400634E8D1DD424540B5FEE6A37FCD4540737979F3DDCC2CC0F92154F9E7534540'::geometry) > >>> > >>> I believe this is because there are infinite values in that geometry: > > Since nobody appears to be too interested in producing a quick fix in > > geos, attached is a patch that puts in an *ugly* workaround in > > PostGIS, that simply rejects the infinite values higher up in the > > stack. Please see http://trac.osgeo.org/geos/ticket/357 It was waiting for a testcase, as none helped reproducing with 3.3.0rc1 so far. I'll try with the data you give. It may need a second geometry to tango. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Tue, Mar 8, 2011 at 10:16, Magnus Hagander wrote:
> On Mon, Feb 28, 2011 at 16:35, Mark Cave-Ayland
> wrote:
>> On 28/02/11 15:18, Magnus Hagander wrote:
>>
>>> Hi!
>>>
>>> Running the following query locks up postgis completely (in
>>> geos::algorithm::RobustDeterminant):
>>>
>>> SELECT st_intersects(somegeometry,
>>>
>>> '010320E61001000500737979F3DDCC2CC0F92154F9E7534540F07FF07F8F806E993F7E55C0304B29FFEA8554400634E8D1DD424540B5FEE6A37FCD4540737979F3DDCC2CC0F92154F9E7534540'::geometry)
>>>
>>> I believe this is because there are infinite values in that geometry:
>>>
>>> # select
>>> ST_AsText('010320E61001000500737979F3DDCC2CC0F92154F9E7534540F07FF07F8F806E993F7E55C0304B29FFEA8554400634E8D1DD424540B5FEE6A37FCD4540737979F3DDCC2CC0F92154F9E7534540'::geometry);
>>>
>>> st_astext
>>>
>>> ---
>>> POLYGON((-14.4001308522972 42.6555167828373,inf inf,-85.9726317957995
>>> 82.0924680617579,42.5223944076352 43.6054577711015,-14.4001308522972
>>> 42.6555167828373))
>>> (1 row)
>>>
>>>
>>> ISTM that this should either be rejected as an invalid geometry, or at
>>> least not hang
>>
>> Hi Magnus,
>>
>> H - I can definitely reproduce this on trunk with GEOS 3.2 series. The
>> backtrace from inside GEOS looks like this:
>
> Since nobody appears to be too interested in producing a quick fix in
> geos, attached is a patch that puts in an *ugly* workaround in
> PostGIS, that simply rejects the infinite values higher up in the
> stack. I don't consider this a long-term fix, but it at least causes
> an error instead of getting stuck in an infinite loop that can only be
> terminated with kill -9... So pending a solution in geos, I would
> suggest this workaround (or something better located written by
> someone who actually know the postgis code better than me) be put in.
> The way it is now, any application that allows the user to specify
> input that could generate such a geometry could trivially
> denial-of-service any postgis site...
My customer has now been running with this patch in full productoin
for a couple of weeks, and reports that it has solved the problem for
them, with no ill effects.
Even if there is a solution eventually put down in the underlying
libraries, I believe this patch should be in PostGIS. For one, it's
going to take a while for the downstream fixes to trickle into all
distributions. And if/when they *are* fixed, the overhead will be
basically zero overhead.
And it *is* protecting against a boundary condition that basically
requires a restart of the whole database system - so I still claim
it's a bug in PostGIS *as well* in that case. So having thought some
more about it, it's actually not ugly imho :D
Anyway.
I humbly request that this patch - or equivalent functionality - be
applied to PostGIS both trunk and the branch for the next 1.5.x
release (assuming there is one).
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
