Re: [GENERAL] Recovery.conf and PITR
On Fri, Aug 9, 2013 at 2:17 PM, ascot.m...@gmail.com wrote: > Is there a way to query the master (it is up and running) about the actual > commit sequence by transaction IDs? The information is within the clogs, but nothing comes into my mind as to inspect from an admin point of view the clog sequence. Anyway, I suepect it is wrong the approach you have with recovery: you are asking the database to recover at least up to transaction x, so why worrying about other interleaved transactions? Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] earthdistance
To calculate geographic distances, a great circle route calculation is used. This is not the same thing as the simple calculation for distances between two points on a sphere. In order to perform the calculation, various model parameters must be chosen, such as the ellipsoidal reference model. To see examples of how this calculation is performed, see the code from the project proj.4: https://trac.osgeo.org/proj/ I see that PostGIS uses proj.4, so if you have installed that package, you should be able to perform the calculations you wish to make. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Olivier Chaussavoine Sent: Friday, August 9, 2013 12:30 PM To: pgsql-general@postgresql.org Subject: [GENERAL] earthdistance I develope a project openbarter that needs to match objects based on a maximum distance between their positions on earth. I saw that the documentation of the extension earthdistance was interesting, but the promise was not in the code. It would be nice to have these functions available independently of sophisticated geographic systems. There is a circle object for flat two dimensional space, but earth deals with spherical caps. It would not be exact but enough to suppose that earth is a sphere and that all dimensions latitude, longitude and distance are in radian. What would need to be done to adapt the circle type to a new type 'spherical cap' that would allow simple geographic indexing? -- Olivier Chaussavoine
Re: [GENERAL] Need Help
On Aug 9, 2013, at 19:59, nandan wrote: > Hello All ; > > Please help me in knowing below queries which are in Mysql to Postgresql. Postgres and MySQL are very different in these respects. Answering these questions is rather pointless, as it will not give you an understanding of how the role and grant system works in PostgreSQL and you'll need that kind of understanding if you're as concerned about security as you appear to be. Have a look at chapters 19 & 20 in the fine manual: http://www.postgresql.org/docs/9.2/static/index.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] earthdistance
As a simple potential user, I tried to install PostGIS, downloaded all libraries required: proj-4.8.0, gdal-1.10.0,json-c,postgis-2.0.3,geos-3.3.8,libwml2-2.9.0, and tried to build the first library with the simple procedure: ./configure make make install I had a fatal error: make[2]: entrant dans le répertoire « /home/olivier/ob/proj-4.8.0/src » /bin/bash ../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c -o jniproj.lo jniproj.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c jniproj.c -fPIC -DPIC -o .libs/jniproj.o jniproj.c:52:26: fatal error: org_proj4_PJ.h: No such file or directory compilation terminated. problem out of the scope of this list, and probably not /difficult. Since I look for a simple geographic indexing using imprecise lat,long coordinates that do not deal with precise modeling; that I am afraid of long install procedure, and heavy computations, I also give up. Spacial mysql indexing seems to be included in pre-built packages. What can we do? 2013/8/10 John R Pierce > On 8/9/2013 5:18 PM, Brent Wood wrote: > > You might install Postgis to implement very powerful spatial functionality > that can easily do what you are asking (plus a whole lot more). > > > > indeed, PostGIS is the logical answer, but the OP specifically stated he > wanted the functionality without 'sophisticated geographic systems'. so I > ignored the question. > > the alternative would be implementing your own spherical geometry > functions, and hook them up to GiST indexing, its not *that *hard, but by > the time you got all the functionality you need, you'd be half way to > PostGIS, so why fight it? > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > -- Olivier Chaussavoine
Re: [GENERAL] earthdistance
I did not found any geographic indexing with earthdistance, and need it. The need I have is simple: "is the distance between two (lat,long) positions less than X km?" the model used for the shape of the earth should be related to the precision of lat,lon, and most sources are imprecise. The spherical model should be enough. How wrong I am to think that: * postgres contains a circle object where distance is the hypothenuse. The spherical problem using lat,long is also bidimensional. We could /just use haversine distance instead of hypothenuse. * gist and b-tree bidimensional indexing does not need to be changed to fulfill this need. 2013/8/10 Uwe Schroeder > ** > > > > How accurate do you need it? My website has a lot of "local" listing stuff > based on a distance from the viewer and I use the earthdistance module in > contrib to do it. > > Given, it's not accurate enough to calculate a surgical missile strike, > but for "within 20 miles" type of things it's good enough - give or take a > mile. > > > > The problem I have is to determine the location of the viewer/poster. > Aside from asking precice lat/long coordinates (which no user will have any > clue about), the next best thing is to rely on smartphones and their GPS - > but what of regular browser/computer users? When I let google map my > location it shows as San Francisco - which is about 56 miles off. So > network location makes no sense for this. > > I'm using a zipcode based geographical mapping, which already has flaws > since a zipcode is an area, not a point. The commonly available zipcode > databases give you the geographical center of the zipcode - which certainly > will be some distance off from the real location. > > > > I found that the inaccuracies work for my application - nobody cares about > a few more or less miles when looking for something. The advantage is that > it also protects the privacy of the poster to some degree - nobody really > needs to know exactly where the post originated... > > > > If "openbarter" is what I think it is (kinda craigslist just with > bartering), I think a few miles off won't make a difference. Chances are, > your members won't divulge their true location anyways. We have members > from South Africa using a US zipcode - which is what ... 5000 miles off? > > Earthdistance is definitely easy to deal with - just give a > latitude/longitude and off you go.. > > > > Uwe > > > > > > > > > > On Fri, 08/09/2013 09:29:49 PM Olivier Chaussavoine wrote: > > I develope a project openbarter that needs to match objects based on a > maximum distance between their positions on earth. I saw that the > documentation of the extension earthdistance was interesting, but the > promise was not in the code. It would be nice to have these functions > available independently of sophisticated geographic systems. There is a > circle object for flat two dimensional space, but earth deals with > spherical caps. It would not be exact but enough to suppose that earth is a > sphere and that all dimensions latitude, longitude and distance are in > radian. > What would need to be done to adapt the circle type to a new type > 'spherical cap' that would allow simple geographic indexing? > > -- > Olivier Chaussavoine > > > > -- Olivier Chaussavoine
Re: [GENERAL] How To Install Extension Via Script File?
Chris Travers writes: > As for whether UTF-8 is the default, it is in many cases, but I remember > struggling with the fact that a few Linux distros still default to > SQL-ASCII. Ultimately this is something of a packaging issue and the > default may be set at the package level. Actually, the default is taken from the locale environment that initdb sees. So it's a question of what the distro initializes LANG to (and whether you've changed that, either system-wide or for the postgres user). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 9.3 beta log
On Sat, Aug 10, 2013 at 06:38:57AM +, Arvind Singh wrote: > as you can see it is for a single record "login88193" it places > > PARSER STATISTICS > PARSE ANALYSIS STATISTICS > REWRITER STATISTICS > > I know it appers because i have enabled log_parser_* options in log > > But what i request to know is the meaning of these statistics and how usefull > is can be for a server load evaluation You should look get the getrusage manual pages for the meaning of the values, and perhaps this for the meaning of these stages of a query: http://www.postgresql.org/developer/backend/ -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Convincing the query planner to play nice
Hi all, I seem to be having some grief with the 9.1.9 query planner favouring an index scan + merge join, over a sequential scan + hash join. Logically I would have considered the index+merge to be faster, as suggested by the explain output - but in practice, it is in fact slower by orders of magnitude. In my timings below, I've tried to reduce the impact of any OS or shared_buffer level caching (restarting postgres, and flushing OS cache between queries-). I've provided my settings as shown: =# show seq_page_cost; seq_page_cost --- 1 (1 row) Time: 0.355 ms =# show random_page_cost; random_page_cost -- 2.2 (1 row) Time: 0.084 ms =# show cpu_tuple_cost; cpu_tuple_cost 0.01 (1 row) Time: 0.077 ms =# show cpu_index_tuple_cost; cpu_index_tuple_cost -- 0.005 (1 row) Time: 0.065 ms =# show cpu_operator_cost; cpu_operator_cost --- 0.0025 (1 row) Time: 0.064 ms =# show effective_cache_size; effective_cache_size -- 12GB (1 row) -- QEP's for 9.1.9 =# explain (analyse,buffers) select * from archive.users inner join live.addresses using (address_id); QUERY PLAN Merge Join (cost=18.79..159615.38 rows=1887786 width=131) (actual time=0.023..602386.955 rows=1862872 loops=1) Merge Cond: (addresses.address_id = users.address_id) Buffers: shared hit=1655113 read=382486 -> Index Scan using addresses_pkey on addresses (cost=0.00..52609.75 rows=1872220 width=22) (actual time=0.008..1440.294 rows=1872220 loops=1) Buffers: shared hit=473352 read=18328 -> Index Scan using address_id_users on users (cost=0.00..3075311.08 rows=73741592 width=117) (actual time=0.005..598455.258 rows=1862873 loops=1) Buffers: shared hit=1181761 read=364158 Total runtime: 602548.352 ms (8 rows) Time: 603090.399 ms =# set enable_indexscan=off; SET Time: 0.219 ms =# explain (analyse,buffers) select * from archive.users inner join live.addresses using (address_id); QUERY PLAN -- Hash Join (cost=55340.95..2707034.64 rows=1887786 width=131) (actual time=1279.659..36886.595 rows=1862872 loops=1) Hash Cond: (users.address_id = addresses.address_id) Buffers: shared hit=6 read=1079019 -> Seq Scan on users (cost=0.00..1803222.92 rows=73741592 width=117) (actual time=5.082..26430.189 rows=73741544 loops=1) Buffers: shared hit=2 read=1065805 -> Hash (cost=31938.20..31938.20 rows=1872220 width=22) (actual time=1273.432..1273.432 rows=1872220 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 112381kB Buffers: shared hit=2 read=13214 -> Seq Scan on addresses (cost=0.00..31938.20 rows=1872220 width=22) (actual time=7.190..553.516 rows=1872220 loops=1) Buffers: shared hit=2 read=13214 Total runtime: 37014.912 ms (11 rows) Time: 37518.029 ms The only way I can artificially convince the planner to choose the sequential scan method is to increase cpu_index_tuple_cost from 0.005 to 1.4 This suggests something is really really wrong with the statistics on this table, as that shouldn't be necessary. Interestingly, on another instance of this same database running on postgres 8.3.8, the query planner correctly chooses the sequential scan method - having more sane cost estimates for the index scan method. -- QEP\s for 8.3.8 =# explain select * from archive.users inner join live.addresses using (address_id); QUERY PLAN Hash Join (cost=55340.95..2783655.68 rows=1949180 width=133) Hash Cond: (users.address_id = addresses.address_id) -> Seq Scan on users (cost=0.00..1879254.32 rows=73739432 width=119) -> Hash (cost=31938.20..31938.20 rows=1872220 width=22) -> Seq Scan on addresses (cost=0.00..31938.20 rows=1872220 width=22) (5 rows) =# set enable_seqscan=off; SET =# explain select * from archive.users inner join live.addresses using (address_id); QUERY PLAN - Merge Join (cost=6.98..3496768.28 rows=1949180 width=133) Merge Cond: (addresses.address_id = users.address_id) -> Index Scan using addresses_pke
Re: [GENERAL] Postgres 9.3 beta log
On 08/10/2013 04:21 PM, Bruce Momjian wrote: > On Sat, Aug 10, 2013 at 06:38:57AM +, Arvind Singh wrote: >> as you can see it is for a single record "login88193" it places >> >> PARSER STATISTICS >> PARSE ANALYSIS STATISTICS >> REWRITER STATISTICS >> >> I know it appers because i have enabled log_parser_* options in log >> >> But what i request to know is the meaning of these statistics and how usefull >> is can be for a server load evaluation > > You should look get the getrusage manual pages for the meaning of the > values, and perhaps this for the meaning of these stages of a query: > > http://www.postgresql.org/developer/backend/ true - but it seems a bit dubious that all of them show basically zero (or a constant time) Stefan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replication delay
Hi, I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica. >From time to time the replica itself is too busy, all read-only queries will >get inconsistent results because of replication lag, sometimes it can be >longer than 6 minutes. I am thinking to add multiple replicas to off-load >read-only queries, can you please suggest a way to monitor and failover the >read-only query when the replication lag in a replica is more than 5 minutes? regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 9.3 beta log
On Sat, Aug 10, 2013 at 09:52:48PM +0200, Stefan Kaltenbrunner wrote: > On 08/10/2013 04:21 PM, Bruce Momjian wrote: > > On Sat, Aug 10, 2013 at 06:38:57AM +, Arvind Singh wrote: > >> as you can see it is for a single record "login88193" it places > >> > >> PARSER STATISTICS > >> PARSE ANALYSIS STATISTICS > >> REWRITER STATISTICS > >> > >> I know it appers because i have enabled log_parser_* options in log > >> > >> But what i request to know is the meaning of these statistics and how > >> usefull > >> is can be for a server load evaluation > > > > You should look get the getrusage manual pages for the meaning of the > > values, and perhaps this for the meaning of these stages of a query: > > > > http://www.postgresql.org/developer/backend/ > > true - but it seems a bit dubious that all of them show basically zero > (or a constant time) Yes, in an ideal world, the majority of time should be spent in the executor. Those earlier stages are designed to set thing up for the executor. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convincing the query planner to play nice
Timothy Kane writes: > I seem to be having some grief with the 9.1.9 query planner favouring an > index scan + merge join, over a sequential scan + hash join. I believe the reason it's preferring the merge join plan is that it thinks the executor will be able to terminate the merge join early as a consequence of the range of join keys in "addresses" being only a fraction of the range of join keys in "users". Notice that the total estimated cost for the merge join is just a fraction of the full estimated cost of the indexscan on "users"; the only way that's possible is if the indexscan on "users" doesn't have to run through all of the table. Probably, the range of join keys is wider than the planner thinks and so the merge join can't terminate early. The fix therefore is to crank the stats target for "addresses" up high enough that you get a reasonable value in pg_statistic for the largest address_id value (look at the last histogram entry). > Interestingly, on another instance of this same database running on postgres > 8.3.8, the query planner correctly chooses the sequential scan method - > having more sane cost estimates for the index scan method. I think the 8.3 planner didn't take this effect into account. Or maybe it did, but by chance the upper histogram value is closer to reality on the older database. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convincing the query planner to play nice
Okay, so I've played with this a bit more. I think I have it cracked. I had to increase random_page_cost and significantly reduce effective_cache_size in order for the planner to prefer a sequential scan. (It turns out this is what the 8.3.8 instance was doing all along, so it's not anything specific to 9.1.9). Assuming that effective_cache_size has no bearing on postgres behaviour outside of query planning, then I guess that's ok. It must be simply that the index based method causes a lot of random order reads of the relation. A better way however, seems to be clustering the table based on the address_id index. This seems to have done the job nicely, allowing the pages to be read in the order they're recorded on disk. In fact, it performs incredibly well now. Who knew! :) =# explain (analyse,buffers) select * from archive.users inner join live.addresses using (address_id); QUERY PLAN -- Merge Join (cost=756.82..151865.53 rows=1982043 width=131) (actual time=6.417..3851.314 rows=1862872 loops=1) Merge Cond: (addresses.address_id = users.address_id) Buffers: shared hit=10 read=65799 -> Index Scan using addresses_pkey on addresses (cost=0.00..52602.26 rows=1872220 width=22) (actual time=0.011..638.291 rows=1872220 loops=1) Buffers: shared hit=6 read=18328 -> Index Scan using address_id_users on users (cost=0.00..2630954.66 rows=74700184 width=117) (actual time=6.391..1657.213 rows=1862873 loops=1) Buffers: shared hit=4 read=47471 Total runtime: 3954.146 ms (8 rows) Again, just thinking out loud here.. In a scenario where specific clustering isn't an option... I wonder if the query planner should consider the physical distribution/ordering of values on disk, and use that as a factor when applying the random_page_cost in the QEP's? I'm sure I've missed something fundamental here, happy to be corrected :) Cheers, Tim On 10 Aug 2013, at 18:32, Timothy Kane wrote: > > Hi all, > > I seem to be having some grief with the 9.1.9 query planner favouring an > index scan + merge join, over a sequential scan + hash join. > Logically I would have considered the index+merge to be faster, as suggested > by the explain output - but in practice, it is in fact slower by orders of > magnitude. > > In my timings below, I've tried to reduce the impact of any OS or > shared_buffer level caching (restarting postgres, and flushing OS cache > between queries-). > > I've provided my settings as shown: > > > =# show seq_page_cost; > seq_page_cost > --- > 1 > (1 row) > > Time: 0.355 ms > =# show random_page_cost; > random_page_cost > -- > 2.2 > (1 row) > > Time: 0.084 ms > =# show cpu_tuple_cost; > cpu_tuple_cost > > 0.01 > (1 row) > > Time: 0.077 ms > =# show cpu_index_tuple_cost; > cpu_index_tuple_cost > -- > 0.005 > (1 row) > > Time: 0.065 ms > =# show cpu_operator_cost; > cpu_operator_cost > --- > 0.0025 > (1 row) > > Time: 0.064 ms > =# show effective_cache_size; > effective_cache_size > -- > 12GB > (1 row) > > > > -- QEP's for 9.1.9 > =# explain (analyse,buffers) select * from archive.users inner join > live.addresses using (address_id); > >QUERY > PLAN > > Merge Join (cost=18.79..159615.38 rows=1887786 width=131) (actual > time=0.023..602386.955 rows=1862872 loops=1) >Merge Cond: (addresses.address_id = users.address_id) >Buffers: shared hit=1655113 read=382486 >-> Index Scan using addresses_pkey on addresses (cost=0.00..52609.75 > rows=1872220 width=22) (actual time=0.008..1440.294 rows=1872220 loops=1) > Buffers: shared hit=473352 read=18328 >-> Index Scan using address_id_users on users (cost=0.00..3075311.08 > rows=73741592 width=117) (actual time=0.005..598455.258 rows=1862873 loops=1) > Buffers: shared hit=1181761 read=364158 > Total runtime: 602548.352 ms > (8 rows) > > Time: 603090.399 ms > > > > =# set enable_indexscan=off; > SET > Time: 0.219 ms > =# explain (analyse,buffers) select * from archive.users inner join > live.addresses using (address_id); > QUERY PLAN > > ---
Re: [GENERAL] Convincing the query planner to play nice
Ahh, thanks Tom. I hadn't seen your email before I posted my own followup. I guess the clustering approach managed to work around the need to mess with the statistics target. I did previously increase the target to 1000 (from 100) for that field and had no impact, but this is an aspect of tuning I'm not so familiar with - I didn't consider pushing it all the way to 11. On 11 Aug 2013, at 00:28, Tom Lane wrote: > Timothy Kane writes: >> I seem to be having some grief with the 9.1.9 query planner favouring an >> index scan + merge join, over a sequential scan + hash join. > > I believe the reason it's preferring the merge join plan is that it thinks > the executor will be able to terminate the merge join early as a > consequence of the range of join keys in "addresses" being only a fraction > of the range of join keys in "users". Notice that the total estimated > cost for the merge join is just a fraction of the full estimated cost of > the indexscan on "users"; the only way that's possible is if the indexscan > on "users" doesn't have to run through all of the table. Probably, the > range of join keys is wider than the planner thinks and so the merge join > can't terminate early. The fix therefore is to crank the stats target for > "addresses" up high enough that you get a reasonable value in pg_statistic > for the largest address_id value (look at the last histogram entry). > >> Interestingly, on another instance of this same database running on postgres >> 8.3.8, the query planner correctly chooses the sequential scan method - >> having more sane cost estimates for the index scan method. > > I think the 8.3 planner didn't take this effect into account. Or maybe it > did, but by chance the upper histogram value is closer to reality on the > older database. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convincing the query planner to play nice
Tim Kane writes: > I guess the clustering approach managed to work around the need to mess with > the statistics target. I did previously increase the target to 1000 (from > 100) for that field and had no impact, but this is an aspect of tuning I'm > not so familiar with - I didn't consider pushing it all the way to 11. Yeah, I had actually started to write an email recommending that you dial down effective_cache_size and increase random_page_cost, before I noticed the discrepancy in the merge join cost and realized what was really going on. The question now is why you had those settings like that before, and whether changing them back in the direction of the defaults might not be pessimizing the behavior for other queries. If you have a lot of RAM and mostly-cached queries, the previous settings didn't sound unreasonable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convincing the query planner to play nice
Yep, the effective_cache_size was specifically because we have lots of RAM to play with, and want to ensure we are caching wherever possible. The reduced random_page_cost was primarily to allow for the fact we're using relatively fast disk (indexes are SSD, table data on SAS drives), though I didn't fully appreciate how the combination of these settings can influence the preference towards a sequential vs index scan. I think i'll stop tweaking for now, and see how it performs in the next few days. I feel like I have a much better handle on how the planner is pulling everything together. Cheers. Tim On 11 Aug 2013, at 01:38, Tom Lane wrote: > Tim Kane writes: >> I guess the clustering approach managed to work around the need to mess with >> the statistics target. I did previously increase the target to 1000 (from >> 100) for that field and had no impact, but this is an aspect of tuning I'm >> not so familiar with - I didn't consider pushing it all the way to 11. > > Yeah, I had actually started to write an email recommending that you dial > down effective_cache_size and increase random_page_cost, before I noticed > the discrepancy in the merge join cost and realized what was really going > on. > > The question now is why you had those settings like that before, and > whether changing them back in the direction of the defaults might not be > pessimizing the behavior for other queries. If you have a lot of RAM and > mostly-cached queries, the previous settings didn't sound unreasonable. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] earthdistance
Is there not a precompiled Postgis package you can use? There are a few dependencies, the PROJ.4 libraries you are missing enable projection support, and the package tools automatically manage such dependencies. I know packages are well supported for Debian, Ubuntu/Mint/etc, Suse & Fedora. See: http://trac.osgeo.org/postgis/wiki/UsersWikiInstall Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Olivier Chaussavoine [olivier.chaussavo...@gmail.com] Sent: Saturday, August 10, 2013 10:17 PM To: John R Pierce Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] earthdistance As a simple potential user, I tried to install PostGIS, downloaded all libraries required: proj-4.8.0, gdal-1.10.0,json-c,postgis-2.0.3,geos-3.3.8,libwml2-2.9.0, and tried to build the first library with the simple procedure: ./configure make make install I had a fatal error: make[2]: entrant dans le répertoire « /home/olivier/ob/proj-4.8.0/src » /bin/bash ../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c -o jniproj.lo jniproj.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c jniproj.c -fPIC -DPIC -o .libs/jniproj.o jniproj.c:52:26: fatal error: org_proj4_PJ.h: No such file or directory compilation terminated. problem out of the scope of this list, and probably not /difficult. Since I look for a simple geographic indexing using imprecise lat,long coordinates that do not deal with precise modeling; that I am afraid of long install procedure, and heavy computations, I also give up. Spacial mysql indexing seems to be included in pre-built packages. What can we do? 2013/8/10 John R Pierce mailto:pie...@hogranch.com>> On 8/9/2013 5:18 PM, Brent Wood wrote: You might install Postgis to implement very powerful spatial functionality that can easily do what you are asking (plus a whole lot more). indeed, PostGIS is the logical answer, but the OP specifically stated he wanted the functionality without 'sophisticated geographic systems'. so I ignored the question. the alternative would be implementing your own spherical geometry functions, and hook them up to GiST indexing, its not that hard, but by the time you got all the functionality you need, you'd be half way to PostGIS, so why fight it? -- john r pierce 37N 122W somewhere on the middle of the left coast -- Olivier Chaussavoine -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] earthdistance
I just recently built the postgis stack (unbuntu 12.4) and had to go to the trunk (maybe 2.1) for postgis itself for a work-around a dependency failure on, ircc, geos. rjs On 08/10/2013 04:03 PM, Brent Wood wrote: Is there not a precompiled Postgis package you can use? There are a few dependencies, the PROJ.4 libraries you are missing enable projection support, and the package tools automatically manage such dependencies. I know packages are well supported for Debian, Ubuntu/Mint/etc, Suse & Fedora. See: http://trac.osgeo.org/postgis/wiki/UsersWikiInstall Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Olivier Chaussavoine [olivier.chaussavo...@gmail.com] Sent: Saturday, August 10, 2013 10:17 PM To: John R Pierce Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] earthdistance As a simple potential user, I tried to install PostGIS, downloaded all libraries required: proj-4.8.0, gdal-1.10.0,json-c,postgis-2.0.3,geos-3.3.8,libwml2-2.9.0, and tried to build the first library with the simple procedure: ./configure make make install I had a fatal error: make[2]: entrant dans le répertoire « /home/olivier/ob/proj-4.8.0/src » /bin/bash ../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c -o jniproj.lo jniproj.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c jniproj.c -fPIC -DPIC -o .libs/jniproj.o jniproj.c:52:26: fatal error: org_proj4_PJ.h: No such file or directory compilation terminated. problem out of the scope of this list, and probably not /difficult. Since I look for a simple geographic indexing using imprecise lat,long coordinates that do not deal with precise modeling; that I am afraid of long install procedure, and heavy computations, I also give up. Spacial mysql indexing seems to be included in pre-built packages. What can we do? 2013/8/10 John R Pierce mailto:pie...@hogranch.com>> On 8/9/2013 5:18 PM, Brent Wood wrote: You might install Postgis to implement very powerful spatial functionality that can easily do what you are asking (plus a whole lot more). indeed, PostGIS is the logical answer, but the OP specifically stated he wanted the functionality without 'sophisticated geographic systems'. so I ignored the question. the alternative would be implementing your own spherical geometry functions, and hook them up to GiST indexing, its not that hard, but by the time you got all the functionality you need, you'd be half way to PostGIS, so why fight it? -- john r pierce 37N 122W somewhere on the middle of the left coast -- Olivier Chaussavoine -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
