Re: [GENERAL] Development of an extension for PostgreSQL and PostGIS

In order to get an LWGEOM from PostGIS you'll need to convert from the serialized form (GSERIALIZED) which you can read all about in the liblwgeom.h header. You'll be adding a hard dependency of course, but hopefully you're OK with that. If you're just hoping to build a compound type, as your

Re: [GENERAL] Running out of memory the hard way ...

On Wed, Feb 8, 2017 at 7:44 AM, Tom Lane wrote: > Albe Laurenz writes: > > Bill Moran wrote: > >> What I feel is the best way to mitigate the situation, is to have some > >> setting that limits the maximum RAM any backend can consume. > > > I'd

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

your index, > that is partition your table into several tables ! > > This is not easy to do with current postgres partitionning methods as far > as I know > (partitionning is easy, automatic efficient query is hard). > > Another way would be to reduce you requirement, a

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

isr...@ravnalaska.net> wrote: > > On Jan 5, 2017, at 10:38 AM, Paul Ramsey <pram...@cleverelephant.ca> > wrote: > > Yes, you did. You want a query that spits out a tupleset of goemetries > (one each for each wee segment), and then you can join that set to your > main table usin

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

. On Thu, Jan 5, 2017 at 11:36 AM, Israel Brewster <isr...@ravnalaska.net> wrote: > On Jan 5, 2017, at 8:50 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote: > > > The index filters using bounding boxes. A long, diagonal route will have > a large bounding box, relative to

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

The index filters using bounding boxes. A long, diagonal route will have a large bounding box, relative to the area you actually care about (within a narrow strip of the route). Use ST_Segmentize() to add points to your route, ST_DumpPoints() to dump those out as point and ST_MakeLine to generate

Re: [GENERAL] Index impact on update?

You'd be better off forcing the table to write in bulk with something like CREATE TABLE mynewtable AS SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog FROM myoldtable; Then index the new table, rename, etc. Bulk update will, in addition to being slow, use 2x the amount of

Re: [GENERAL] Extensions and privileges in public schema

When you create the student user, remove their create privs in public. Then create a scratch schema and grant them privs there. Finally, alter the student user so that the scratch schema appears FIRST in their search path. This will cause unqualified CREATE statements to create in the scratch

Re: [GENERAL] migrating data from an old postgres version

> On Jul 15, 2016, at 6:55 AM, Melvin Davidson wrote: > > > > On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos > wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from his > old one to

Re: [GENERAL] Update statement results in Out of memory

Running a multi-million row update will take a long time. It's possible you've exposed a memory leak in ST_Buffer (the older your version of GEOS, the more likely that is) but it's also possible you're just running a really long update. I find for batch processing purposes that creating fresh

[GENERAL] PostGIS 2.2.0 Released

http://postgis.net/2015/10/07/postgis-2.2.0 PostGIS 2.2.0 is released! Over the last two years a number of interesting new features have been added, such as: * True nearest-neighbor searching for all geometry and geography types * New volumetric geometry support, including ST_3DDifference,

[GENERAL] relpages for pg_toast.* tables

I've been trying to write up a pg_total_relation_size() replacement that just uses the system tables (sum up relpages for tables, indexes and toast tables), thereby avoiding the overhead of running stat() on thousands of filesystem tables, but I've come up against what seems to be an unsuperable

Re: [GENERAL] relpages for pg_toast.* tables

:16 AM, Paul Ramsey pram...@cleverelephant.ca wrote: I've been trying to write up a pg_total_relation_size() replacement that just uses the system tables (sum up relpages for tables, indexes and toast tables), thereby avoiding the overhead of running stat() on thousands of filesystem tables

[GENERAL] Less is More

Hey all, I have an odd performance quirk I wonder if anyone has any theories for… (not a one-time result, table all heated up, etc) spain=# explain analyze select way from planet_osm_point;                                                         QUERY PLAN

[GENERAL] Re: [postgis-users] Memory management in postgres (with liblwgeom functions in particular)

Igor, Your supposition is is all correct (you might want to ask your questions on postgis-devel, where the developer density is higher). lwalloc by default is just a facade over malloc, for standalone use. But, when used in PostGIS, it it backed by palloc, which is in turn the memory manager

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Circling back on this one, I had a look at our analyze code. I found one place where *maybe* we weren't freeing memory and freed it, but analyzing a 2M record table I barely see any bump up in memory usage (from 22M up to 24M at peak) during analyze. And the change I made didn't appear to alter

Re: [GENERAL] Postgres not using GiST index in a lateral join

Stop writing so many subqueries, think in joins; the poor planner! SELECT DISTINCT ON (a.id) a.id AS a_id, b.id AS b_id FROM a JOIN b ON ST_Contains(b.shape, a.shape) WHERE b.kind != 1 Also, the DISTINCT ON syntax (PgSQL custom) lets you winnow a result set down to just one of the inputs. P.

Re: [GENERAL] Indexing large table of coordinates with GiST

geohash them and index them with a btree instead. The index will work better than a rtree for points, efficiencywise, however you’ll still have a multi-billion record table, which could cause other slowdowns, depending on your plans for accessing this data once you’ve indexed it. P. --  Paul

Re: [GENERAL] Indexing large table of coordinates with GiST

interact with the geohash split points. Also, if you’re planning to slam pretty large polygons through this process, expect it to be kind of slow. You’ll want to do some sharding, to spread the problem out over multiple nodes.   --  Paul Ramsey http://cleverelephant.ca  http://postgis.net -- Sent

[GENERAL] spgist index not getting used

clues? Thanks, P --  Paul Ramsey http://cleverelephant.ca http://postgis.net

Re: [GENERAL] spgist index not getting used

://postgis.net http://cleverelephant.ca On September 24, 2014 at 4:32:35 PM, Peter Geoghegan (peter.geoghega...@gmail.com) wrote: On Wed, Sep 24, 2014 at 2:01 PM, Paul Ramsey wrote: If I build an index on the same table using the internal quad-tree ops, and use their operator, I do get an index scan

Re: [GENERAL] spgist index not getting used

Tom! P. -- http://postgis.net http://cleverelephant.ca On September 24, 2014 at 8:24:02 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: Paul Ramsey writes: My C implementation is here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.c My SQL binding calls

[GENERAL] fmgr_oldstyle in extensions

could have done that is causing my calls to route through fmgr_oldstyle instead of the usual path? P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] fmgr_oldstyle in extensions

--  Paul Ramsey http://cleverelephant.ca http://postgis.net On September 23, 2014 at 10:02:33 AM, Andres Freund (and...@2ndquadrant.com) wrote: You forgot to add a PG_FUNCTION_INFO_V1(yourfunc); for the function. 

Re: [GENERAL] Analyze against a table with geometry columns runs out of memory

probably need a copy of the data, or access to a system that has the data and a dev environment. Please do file a ticket at http://trac.osgeo.org/postgis on this issue. P --  Paul Ramsey http://cleverelephant.ca http://postgis.net On May 7, 2014 at 11:15:10 PM, Roxanne Reid-Bennett (r...@tara

[GENERAL] round(real,integer)

. Is this on purpose, or just an oversight?  Obviously one can work around it, but I'm sick of doing so, it makes my SQL examples ugly. I’d like to fix it, but not if there’s a reason it’s not supposed to be done. P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net -- Sent via pgsql-general

Re: [GENERAL] round(real,integer)

, so perhaps I’ve finally found my bite-size pgsql contribution project :) P. --  Paul Ramsey http://cleverelephant.ca http://postgis.net On January 10, 2014 at 12:37:04 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: Paul Ramsey pram...@cleverelephant.ca writes: History question: Why does

Re: [GENERAL] round(real,integer)

True (?) though I’m guessing the real test for most folks is if printf renders it as expected. Anything else if icing on the cake, no? P --  Paul Ramsey http://cleverelephant.ca http://postgis.net On January 10, 2014 at 1:09:24 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: to, say, 6 digits

Re: [GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

Recommendation: do one at a time. First postgis 1.5 to 2.1, then pg 9.1 to 9.3. P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Thursday, September 19, 2013 at 4:34 PM, fburg...@radiantblue.com wrote: Are there any showstoppers/recommendations/experiences with upgrading

Re: [GENERAL] earthdistance compass bearing

The code for azimuth on a sphere isn't so gnarly you couldn't whip it up in plpgsql, http://trac.osgeo.org/postgis/browser/trunk/liblwgeom/lwgeodetic.c#L924 P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Tuesday, June 18, 2013 at 11:16 AM, Jeff Herrin wrote: I don't

Re: [GENERAL] What's a good way to improve this query?

at each access will still be there. You might want to stuff the query through a sampling profiler (OSX Shark!) and confirm, but I would guess you'll find a lot of cycles spinning in zlib for this query. Paul -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Wednesday, June 5, 2013

Re: [GENERAL] Strange dump/restore effect

Try just loading the 1.5 dump directly into the 2.0 database without the filtering step. It will be immensely noisy with lots of errors and warnings, but with luck you should find your data is there waiting for you when it's done. P On Sun, Sep 23, 2012 at 2:37 PM, Gražvydas Valeika

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

On 2011-02-07, at 11:27 AM, Edoardo Panfili wrote: On 07/02/11 18.55, Paul Ramsey wrote: Well, maybe you could in-place upgrade if you left your PostGIS version at the original and only upgraded the PostgreSQL part, but you aren't doing that, you're also upgrading your PostGIS version

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. configure: WARNING:

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop

Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search

Did the FAA ever publish slides of those talks? Sure wish I could see them... :) P. On 2010-08-11, at 6:58 PM, Bruce Momjian br...@momjian.us wrote: Greg Smith wrote: Greg Williamson wrote: Our tests -- very much oriented at postGIS found Oracle to be between 5 and 15% _faster_ depending on

Re: [GENERAL] locating cities within a radius of another

create table cities ( geog geography, name varchar, id integer primary key ); insert into cities select Geography(ST_SetSRID(ST_MakePoint(lon, lat),4326)) as geog, name, id from mytable; create index cities_gix on cities using gist ( geog ); select st_distance(a.geog, b.geog),

Re: [GENERAL] Large index operation crashes postgres

Occams razor says it's PostGIS. However, I'm concerned about how old the code being run is. In particular, the library underneath PostGIS, GEOS, had a *lot* of memory work done on it over the last year. I'd like to see if things improve if you upgrade to GEOS 3.2. On Fri, Mar 26, 2010 at 9:04 AM,

Re: [GENERAL] Large index operation crashes postgres

Can you do? alter table placex add column geometry_sector integer; update placex set geometry_sector = geometry_sector(geometry); P. On Wed, Mar 24, 2010 at 1:15 PM, Frans Hals fha...@googlemail.com wrote: Hi, running a geo-database from a dump restore where still one of the most important

Re: [GENERAL] Installation of Postgis/postgresql

Nick, You'll find GEOS questions get answered better on the GEOS mailing list (http://lists.osgeo.org/mailman/listinfo/geos-devel), BUT: If ./configure is not finding g++ then odds are, you don't have it installed (type g++ on the commandline, see what happens). Use your package manager to do a

Re: [GENERAL] incorrect restore from pg_dumpall

In order to restore a backup taken with pg_dumpall you'll want to ensure that the postgis installed in your new system is identical to the postgis in your old one. This is because the postgis function definitions will be looking for a particular postgis library name... the name of the library from

Re: [GENERAL] R-Trees in PostgreSQL

Also for one-dimensional ranges, consider contrib/seg P. On Mon, Nov 2, 2009 at 4:41 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2009-11-03 at 00:25 +0100, Viktor Rosenfeld wrote: I'd like to know what kind of functions I have to implement for a R-Tree index on numeric columns, NUMERIC

Re: [GENERAL] Out of memory error

If you are on PostGIS 1.3.4 there are substantial memory leaks in intersects() for point/polygon cases. Upgrading to 1.3.6 is recommended. P On Mon, Jul 6, 2009 at 1:39 PM, Paul Smithpaulsm...@pobox.com wrote: On Mon, Jul 6, 2009 at 3:34 PM, Tom Lanet...@sss.pgh.pa.us wrote: Clearly a memory

Re: [GENERAL] Is there a meaningful benchmark?

http://blog.redfin.com/devblog/2007/11/elephant_versus_dolphin_which_is_faster_which_is_smarter.html Here's another PgSQL/MySQL testimonial, with a spatial twist :) P On Fri, Mar 20, 2009 at 10:15 AM, Greg Smith gsm...@gregsmith.com wrote: On Fri, 20 Mar 2009, Will Rutherdale (rutherw) wrote:

[GENERAL] Custom Type Alignment

Simple question on CREATE TYPE (I hope): When using the alignment option, and setting it to double, what ends up double aligned? VARDATA()? The whole thing datum (so that the alignment of VARDATA() is actually conditioned on the size of VARHDRSZ) ? Thanks, Paul -- Sent via pgsql-general

[GENERAL] 8.0, UTF8, and CLIENT_ENCODING

to strip out illegal UTF8 bytes, so I can pipe my dump through that rather than hand editing it? Thanks, Paul -- Paul Ramsey Refractions Research http://www.refractions.net [EMAIL PROTECTED] Phone: 250-383-3022 Cell: 250-885-0632 ---(end of broadcast

Re: [GENERAL] 8.0, UTF8, and CLIENT_ENCODING

Thanks all for the information. Summary is: - 8.0 wasn't very strict, and allowed the illegal values in, instead of mapping them over into UTF-8 space - the values can be stripped with iconv -c - 8.2 should be more strict I'm in the midst of my upgrade to 8.2 now, hopefully the LATIN1-UTF8

Re: [GENERAL] PostGIS Binary RPM for Red Hat Linux

PgSQL 7.4 is still supported by the latest PostGIS versions. As is PgSQL 8.1 and (when it comes) 8.2. P On 6-Nov-06, at 8:07 AM, Joshua D. Drake wrote: Sandeep Kumar Jakkaraju wrote: Ya... That was my next question ... if i build the source (the tar.gz) on the postgis.org site .. will it

Re: [GENERAL] PostGIS Binary RPM for Red Hat Linux

Any one you want.  Using the latest won't hurt.On 6-Nov-06, at 11:36 AM, Sandeep Kumar Jakkaraju wrote:What version of Postgis should i install for postgres 8.1.5 ??On 11/7/06, Paul Ramsey [EMAIL PROTECTED] wrote:PgSQL 7.4 is still supported by the latest PostGIS versions.As is PgSQL 8.1

Re: [GENERAL] Simulating sequences

to go to something like MySQL, which doesn't even support procedural languages). -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_ ---(end of broadcast)--- TIP 6: Have

Re: [GENERAL] Upgrading to 7.3.4?

make install initdb -D %PGHOME%/data -- start and restore db. pg_ctl start psql dbackup -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_ ---(end of broadcast

[GENERAL] pg_dump all tables in 7.3.X

We are trying to do an all tables dump using the 7.3.3 pg_dump, but are getting no love. The pg_dump command which worked before, in 7.2, no longer returns any tables: pg_dump -t * dbname Is this by design, or by accident? Paul -- __ / | Paul Ramsey | Refractions

Re: [GENERAL] Failure to install 7.3.3

wrote: I get an error during config I have redhat 7.2. readline library and Zlib library not found...they are installed with the latest version how do I fix this? -Dan -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632

Re: [GENERAL] PostgreSQL On the Mac? OS9 or OSX?

Indeed, on OSX. http://www.pgmac.com Israel Evans wrote: In looking over the documentation, I haven't seen any evidence of PostgreSQL working on the Mac. Is this the case, or am I missing something. Has anyone heard of it ever being used on a mac? Thanks, ~Israel~

[GENERAL] Support for Geometric Types

may be able to bring some funding to bear to support this kind of development) Thanks, Paul -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_

[GENERAL] Booleans and Casting

, which is 1 = true, 0 = false (IE, the standard cast for boolean - number). Any advice? -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_