tags: PostgreSQL

Action executed in 0.000

Each Tag

PostgreSQL

Common tags - number of posts

regex (2), index (2), OSM (2), planet (2), database (2), import (2), tuning (2), TIGER (1), map (1), osmosis (1), Linux (1), XAPI (1), jxapi (1), devops (1), PostGIS (1), femenism (1), pgsnapshot (1), RAID (1), programming (1), library (1), pnorman (1), procedural language (1), Akonadi (1), disk (1), render (1), aggregation (1), PHP (1), Python (1), ERD (1), up-to-date (1), beer (1), query (1), citext (1), text (1), mapnik (1), case (1), osm2pgsql (1), GIS (1), tile (1), Areca (1),

2 way join

PostgreSQL, database, PostgreSQL, import, PostgreSQL, index, OSM, PostgreSQL, PostgreSQL, planet, PostgreSQL, regex, PostgreSQL, tuning

3 way join

OSM, PostgreSQL, import, PostgreSQL, import, planet, PostgreSQL, import, tuning, PostgreSQL, index, regex, OSM, PostgreSQL, planet, OSM, PostgreSQL, tuning, PostgreSQL, planet, tuning

4 way join

OSM, PostgreSQL, import, planet, OSM, PostgreSQL, import, tuning, PostgreSQL, import, planet, tuning, OSM, PostgreSQL, planet, tuning

5 way join

OSM, PostgreSQL, import, planet, tuning

●●●●●○○○

Build your own OpenStreetMap Server - Ubuntu 10.04 Lucid Lynx | weait.com

SUNY Buffalo campus as seen on OpenStreetMap.org in March of 2009

Build your own OpenStreetMap server. Build your own what?

OpenStreetMap is the editable World map of everything. It is the Wikipedia of maps. It is to other on-line maps as Wikipedia is to Britannica. And it is awesome in every possible way.

The current version of this article is based on Ubuntu 10.04 Lucid Lynx. Lucid will be released on 29 April 2010 and be in beta until then. Operating systems in beta are not suitable for production use.

url: http://weait.com/content/build-your-own-openstreetmap-server-lucid

type: article, format: blog

●●●●●○○○

Loading a Pgsnapshot Schema With a Planet: Take 2 - Paul's Blog

Awhile back I ran my own jxapi server, running with postgresql 8.4. I recently upgraded my server, and in the process I decided to re-import the database into postgresql 9.1.

url: http://www.paulnorman.ca/blog/2011/11/loading-a-pgsnapshot-schema-with-a-planet-take-2/

type: article, format: blog

●●●●●○○○

selena deckelmann | Tending The Garden: a blog about postgres, open source and the web.

selena deckelmann

Selena Deckelmann is a major contributor to PostgreSQL and a data architect at Mozilla. She’s been involved with free and open source software since 1995 and began running conferences for PostgreSQL in 2007. In 2012, she founded PyLadiesPDX, a portland chapter of PyLadies. She founded Open Source Bridge, Postgres Open and speaks internationally about open source, databases and community. You can find her on twitter (@selenamarie) and on her blog.

url: http://www.chesnok.com/daily/

type: person, format: blog

●●●●●○○○

select * from depesz;

tags: PostgreSQL
DSHL logo

url: http://www.depesz.com/

type: none, format: blog

●●●●●○○○

select * from depesz; » Blog Archive » speeding up like ‘%xxx%’

DSHL logo

as most of you know postgresql can easily speedup searches using:

general idea is to use some kind of full text search/indexing – tsearch, lucene, sphinx, you name it.

but sometimes you can’t install fts/fti, or it doesn’t really solve your problem. is there any help? let’s find out.

url: http://www.depesz.com/index.php/2007/09/15/speeding-up-like-xxx/

type: article, format: blog

●●●●●○○○

select * from depesz; » Blog Archive » indexable ” field like ‘%something’”

DSHL logo

for the long time everybody knew that you can’t use index on “LIKE” operations.

then came text_pattern_ops, so we could use indexes for prefix searches:

but what if i’d like to search for ‘%something’? not prefix, but suffix. in my example – what can i do to use indexes when searching for people from given domain?

url: http://www.depesz.com/index.php/2007/07/30/indexable-field-like-something/

type: article, format: blog

●●●●●○○○

PL/php - WikiStart - public.commandprompt.com

Welcome to the PL/php site. PL/php is a procedural language add-on for PostgreSQL, which means you can define PHP functions that run inside a PostgreSQL server.

url: https://public.commandprompt.com/projects/plphp/wiki

type: project, format: wiki

●●●●●○○○

Command Prompt Inc.

tags: PostgreSQL
CMD | Command Prompt, Inc. - PostgreSQL Solutions, Support & Hosting

Command Prompt, Inc. is the oldest and largest dedicated PostgreSQL support provider in North America. Since 1997, we have been developing, supporting, deploying and advocating the use of the World's Most Advanced Open Source Database.

We are an Oregon corporation who enjoys the benefits of no outside investors, no debt, and being profitable, since our first day of business in 1997.

url: http://commandprompt.com/

type: company, format: none

●●●●●○○○

Akonadi: Database Design

Akonadi ERD

This is an overview of the database layout of the storage server. The schema gets generated by the server using the helper class DbInitializer, based on the definition found in server/src/storage/akonadidb.xml.

url: http://api.kde.org/kdesupport-api/kdesupport-apidocs/akonadi/html/akonadi_server_database.html

type: article, format: blog

●●●●●○○○

Group By Quarter Hour - zillablog

The problem we're solving is the need to do group by on time intervals of less than 1 hour.

url: http://www.xzilla.net/blog/2007/Aug/Group-By-Quarter-Hour.html

type: project, format: blog

●●●●●○○○

PostGIS : Home

PostGIS logo PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension. PostGIS follows the OpenGIS "Simple Features Specification for SQL" and has been certified as compliant with the "Types and Functions" profile.

url: http://postgis.refractions.net/

type: project, format: page

●●●●●●○○

select * from depesz;» Blog Archive » Waiting for 8.4 - case insensitive text ( citext )

On 29th of July, Tom Lane committed patch written by David E. Wheeler, which added new contrib module: citext.

url: http://www.depesz.com/index.php/2008/08/10/waiting-for-84-case-insensitive-text-citext/

type: article, format: blog

●●●●●●○○

Greg Smith's Note Magnet: Linux disk failures: Areca is not so SMART

One of the most frequently asked questions on the PostgreSQL Performance list is "what disk controller works best with PostgreSQL?" It's a tough question; every product has different things it's good and bad at, and it can be hard to through all that to figure out what makes sense for your application. You need to focus equally on performance and reliability, as disk drives are very prone to failure.

url: http://notemagnet.blogspot.com/2008/08/linux-disk-failures-areca-is-not-so.html

type: article, format: blog

Programming

After a long day at work of arduous programming, i come home to do what? Yes more programming. But wait there's a difference. At home, i can sip my beer while waiting for code to compile.

At work today i was able to take the Tiger dataset and draw a map. Tiger comes with only line data, ie. only one dimensional. However for each chain of points there are attributes referring to adjacent polygons. Using tigerpoly.py in the GDAL project i was able to construct the 2 dimensional polygons.

All that data was inserted into a PostgreSQL database with PostGIS installed. PostGIS and GDAL needed a couple patches. Then i wrote a Java program to grab the data using JDBC and drew it using AWT. Sure Java2D is better, but i'm not sure i need it. After all i'm just drawing lines and polygons. Sheesh.

I'm guessing all map data comes from the same source. The Census Bureau. Therefore, even Google (eventually) gets their data from Tiger. How is it different? Well (1) the roads have width proportional to the number of lanes, (2) all the corners are rounded, and (3) their roads are labelled. Those are the major differences in appearance. As for differences in feel, don't get me started.

And on a side note, it's been so long that i almost forgot my password to my own website. Back to dinner (Otter Creek Alpine Ale).