The three main problems that need to be overcome are that a) GEOS won't compile with gcc on OS X and needs the 'clang' compiler instead, and b) PostgreSQL expects all extensions to be dual-architecture (i.e i386 and x86_64) which by default PostGIS (and dependencies) don't get built as and finally c) using the correct bundle loader when linking PostgGIS.

Typically the error manifests itself when building PostGIS as :

ld: warning: ignoring file /usr/bin/postgres, file was built for unsupported file format which is not the architecture being linked (x86_64)
Undefined symbols for architecture x86_64:
  "_CurrentMemoryContext", referenced from:

So, firstly download the source for these projects :

PROJ-4.8.0

GEOS-3.3.3

and of course PostGIS

PostGIS 1.5.3

Uncompress the three archives to separate folders, proj-4.8.0, geos-3.3.3 and postgis-1.5.3.

Now, follow these steps to build the PROJ4 library:

cd proj-4.8.0
./configure CC=/usr/bin/clang CXX=/usr/bin/clang++ "CFLAGS=-arch x86_64 -arch i386" "LDFLAGS=-arch x86_64 -arch i386" "CXXFLAGS=-arch x86_64 -arch i386"
make
sudo make install
cd..

cd geos-3.3.3
./configure CC=/usr/bin/clang CXX=/usr/bin/clang++ "CFLAGS=-arch x86_64 -arch i386" "LDFLAGS=-arch x86_64 -arch i386" "CXXFLAGS=-arch x86_64 -arch i386"
make
sudo make install
cd ..

Then PostGIS itself:

cd postgis-1.5.3
./configure CC=/usr/bin/clang CXX=/usr/bin/clang++ "CFLAGS=-arch x86_64 -arch i386" "LDFLAGS=-arch x86_64 -arch i386" "CXXFLAGS=-arch x86_64 -arch i386"
make "BE_DLLLIBS=-bundle_loader /usr/bin/postgres_real"

Note the parameters to 'make'.  By default the link phase uses '/usr/bin/postgres' as the bundle-loader executable however this doesn't work as Apple have craftily swapped it for a Ruby script and the 'real' Postgres binary is called postgres_real so we need to build against that instead.

At this point (after a few warnings) you should see :

PostGIS was built successfully. Ready to install.

so execute :

sudo make install

At this point it's all standard PostGIS installation however for the sake of completeness here are the next steps :
Add plpgsql to your chosen database (prd in our case) :

$ createlang -U _postgres plpgsql prd

Create the functions, tables and spatial reference data:

$ cd /usr/share/postgresql/contrib/postgis-1.5
$ psql -d prd -U _postgres -f postgis.sql
$ psql -d prd -U _postgres -f spatial_ref_sys.sql

(note the use of _postgres as opposed to postgres).

$ psql -d prd -U _postgres
psql (9.0.5)
Type "help" for help.
prd=# select postgis_full_version();
postgis_full_version
--------------------------------------------------------------------------------------------------
POSTGIS="1.5.3" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.3" USE_STATS
(1 row)
prd=#

That's it! Enjoy.

Luckily iOS (and Mac OS) developers can get beta copies of the new operating systems early so we've upgraded an iPhone 4 and iPad (1) to iOS5 to take a look at the new features (and to check that our "Where It's At" app works on the new release too) and this is what we've found in the first 24 hours, in no particular order.

Apologies for the lack of pictures, technically use of iOS5 is under an Apple NDA and I'm not sure I am allowed to post images.

Twitter integration.

Twitter seem to be Apple's new best friend in this release (as Google was back in the days of early iPhone OS releases) and the service has been integrated into several parts of the OS, namely Safari, Photos and Maps where whatever you happen to be doing with them (looking at a web page, photo or map location) can be tweeted directly with no need for any third party apps.  Your Twitter user name and password are configured in the Settings app (multiple accounts can be setup) and you're then good to go.  There are a couple of peculiar things though; firstly there seems to be no direct way to send a simple text-only tweet.  I would have though adding 'Twitter' or 'Tweet' to Messages would have been a good location for this but I can't seem to find any way of doing this.  Secondly you rather strangely have the option to directly install the official Twitter app from the Settings App. Presumably this is to cover all the other stuff that Twitter does (and one imagines also the way that simple text-based tweets are sent) but it does seem a bit peculiar.

Notifications / Lock screen

Borrowed directly from Android the new 'swipe down' notifications area is much better than the, now rather crude looking, alert based system in iOS4.  Upon a down swipe recent 'events' such as missed calls, new emails, text messages and calendar events are displayed along with the local weather (all in a pretty small font!) in a well laid out view.  Individual items can be removed, and the whole thing is highly configurable.  A similar looking display is used on the Lock Screen and presents a very handy summary of stuff you've missed since the device was locked.  Individual items can be swiped left-to-right to go directly to them as opposed to having to find them later.  This is really nice and for my money actually better implemented than Android.  Apple may not always be first to do something but they're often (although not always) best.  I hope Google don't have a patent on "user swiping down to display a list of notifications"!

One final thing, a welcome addition to the Lock Screen is the ability to go straight to the Camera app.  Press the Home button twice (as per showing the iPod controls) and a small Camera icon appears and takes you directly to it.  The Volume Up button even works as the shutter to make it feel more like a 'proper' camera!

Reminders

This is a new 'built in' App that aims to expand upon Notes (which is still there and appears largely unchanged) and integrate with Calendars in order to provide a more useful 'todo' list.  It definitely has the impression of 'Version 1' software but has a nice touch in that reminders can be based on a location.  Apple's example is that you can set up a reminder to get milk when you're near the store.  This is pretty much how it works as you setup a reminder based on an address in your Contacts list and stipulate wether or not its when you arrive or leave the location.  It seems to work pretty well but you obviously need the address of the location in question.  Going back to Apple's example, who has the address of their local Tesco's in their phone?  This leads me to the two deficiencies in the current product as I see it.  Firstly you should be able to set the location for the reminder based on an arbitrary location (i.e a dropped pin on a map) or a manually entered postal code to make this really useful.  Secondly it seems like iOS considers you to 'be there' when you're within about 500 yds of the location (although this is based on an un-scientific test of walking to my kids school and back!). For some situations this is fine, but for others it'd be good to tighten this up a bit.  For example if I want to set a reminder to 'Lock the door' when I leave my home address I'd rather it did it sooner than later!

iCloud

This much-talked about new service aimed at replacing MobileMe and adding a whole load of new stuff besides certainly looks promising. Personally I never had that much of a problem with MobileMe.  It did what I wanted it to, namely syncing Contacts, Bookmarks and Calendars (although iDisk was slooooow) so I'm a little surprised it's being so dramatically culled.   Anyway, it's not all in Beta yet so I'm a little unsure as to what is supposed to be working and what isn't however I couldn't get App syncing to work (where you buy an App on one device and it magically appears on another that you own) nor could I get Pages and Numbers to store documents in it which I thought Steve Jobs said was ready now.  Anyway.... that can wait, the real kicker for me is that all this goodness is tied in to your Apple ID, so when you use the same ID across devices (Macs, PCs, iPhones, iPads etc) then they all stay in sync with what you've purchased, be that music, apps or books, along with any documents you're working on also.  However I can't believe I'm the only person that has multiple Apple IDs; I use one for work which has a work credit card attached for business-related purchases, and a personal account that I use for music and games etc.  In addition to this I have my MobileMe account which I use for sync services (as above) and personal email.  This all works fine at the moment and causes no problems whatsoever, however when I switch to iOS5 I'm a little concerned about what is going to happen.  In particular the Store setting which allows you to change your Apple ID warns you that you can only change it once every 90 days is not a good sign.  Given that there is no way to merge Apple IDs this could be a problem.

One more thing (well, two)....

A small thing, but the Weather app now has hour-by-hour as part of the next 24 hours which is a nice touch, simply swipe down or tap the 'Hourly' text.

And finally the whole concept of PC-less setup is going to be well received I'm sure, if it's not a year or two late!  Particularly for the iPad which really can be a device in it's own right it makes no sense to require a PC or Mac just to get it working.

All in all the upgrade looks like a good one and the iPhone has certainly got a new lease of life and seems very stable even though the software is still in Beta.  The iPad has crashed a few times however.

We were working on an iPhone app recently and added a Settings.bundle to allow the user to set their preferences as described here and here.  However whenever we ran the app either on the device or on the simulator the settings simply would not appear in the iPhone's settings.

After much digging around and many hours banging heads on walls we finally tracked it down.  Settings bundles are only added to the phone the first time the application is installed. If you've worked on an application and deployed it to your development device or simulator before adding the settings.bundle to your XCode project then subsequent build and runs won't add the settings unless you remove the app from the device or simulator in the usual way. i.e by pressing and holding the app icon and removing it when it starts shaking.

The next time you deploy to the device or simulator the settings should be present.

We hope this helps someone.

To me a simple, yet glaring, omission from the Oracle PL/SQL language, which is in many ways excellent, is a function to test to see if a string represents numeric data as can be found in many other languages (e.g is_numeric in PHP, isNumeric in Java and .Net).

A possible implementation is to simply try to assign the string to a number and catch any exceptions.  If something goes wrong then it's not a number!

  create or replace function isnumeric(p_value in varchar2) return number
  as
    l_ret number;
  begin
    -- Try to cast to a number
    begin
      l_ret := to_number(p_value);
      -- if the line above worked then just reset to 1
      l_ret := 1;
    exception
      when others then
        -- Something, anything, went wrong
        l_ret := 0;
    end;
    -- Return
    return l_ret;
  end isnumeric;

e.g

  select isnumeric('10.1.1') from dual

  select isnumeric('10.1') from dual

  select isnumeric('-10.1') from dual

We hope this helps someone.

Yesterday one of our iPhones developed an intermittent fault with the call quality.  Often we could hear the other party but they couldn't hear us, or vice versa.  It didn't seem to matter who initiated the call or wether it was a mobile to landline call or mobile to mobile.

iPhone 3G

We took the following diagnostic steps to try and track down the problem :

1) Made a sound recording with the Voice Memos app and played it back to confirm speaker / mic working.

2) Powered off and on again

3) Reset (pressed Home and Power button simultaneously for 10 seconds)

4) Did a Restore through iTunes.

All to no effect.  A quick call to O2 resulted in a couple more things to try :

1) Remove SIM card and give it a good clean.  It was surprising how much pocket fluff was in there!

2) Try the SIM card in another phone.

With the second option above we actually found that the other phone (a Nokia) started exhibiting the same behaviour so the problem appeared to be with the SIM, not the iPhone (phew!).

A quick trip to the local O2 retail store for a SIM swap (done in the store with no temporary number or delay) and we're back up and running again.

We hope this helps someone else.

UPDATE : the solution may not have been that simple as the problem resurfaced a few days later.  We've just done a complete factory reset and fingers-crossed we'll be OK.  Just made a 6 minute call which was impossible this morning.

We just upgraded XCode to the latest release (3.1.4 as at time of writing) and noticed that after doing so a number of our iPhone projects now had their Frameworks all set to missing (i.e coloured in red) like this :

The solution wasn't immediately obvious but just in case anyone else was wondering what was required to fix it here are the steps :

1) Open the settings for your project by right-clicking and choosing 'Get Info' :

2) This will display this page, which will display the root of the problem in the 'Base SDK for all configuration' drop down :

The problem is that the project was set to 'iPhone Device 2.2' which is no longer supplied with XCode.

3) To fix the problem simply change the Base SDK to whatever is appropriate for you, e.g :

and voila, the problem is solved :

Thanks for reading.

An earlier post illustrated how to extract information from XML inside PL/SQL.  This post is an example of the reverse of this, in that it shows how to create an XML document from a SQL statement.  These examples are based on creating an XML document providing details of the tables in the SCOTT schema but could easily be adapted to suit your needs.

This first example extract the list of tables names and uses a combination of the XMLRoot, XMLElement, XMLAgg, XMLAttributes and XMLForest functions to create the document :

SELECT XMLRoot(
         XMLElement("Tables",
           XMLAgg(XMLElement("Table",
             XMLAttributes(table_name),
               XMLForest(owner,
                         tablespace_name,
                         status,
                         num_rows)))
       ), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
  from all_tables
 where owner = 'SCOTT'

This example returns a document like so :

<?xml version="1.0" standalone="yes"?>
<Tables>
  <Table TABLE_NAME="DEPT">
    <OWNER>SCOTT</OWNER>
    <TABLESPACE_NAME>USERS</TABLESPACE_NAME>
    <STATUS>VALID</STATUS>
    <NUM_ROWS>4</NUM_ROWS>
  </Table>
</Tables>

etc etc.

To make this a little more useful (and complex!) we can add a nested query to return the column details in-line with the table information, like this :

SELECT XMLRoot(
         XMLElement("Tables",
          XMLAgg(XMLElement("Table",
             XMLAttributes(at.table_name),
               XMLForest(at.owner,
                         at.tablespace_name,
                         at.status,
                         at.num_rows,
                         (select XMLAgg(XMLElement("Column",
                                   XMLAttributes(atc.column_name),
                                     XMLForest(atc.data_type,
                                               atc.data_length,
                                               atc.data_scale,
                                               atc.data_precision,
                                               atc.nullable)))
                           from all_tab_columns atc
                          where atc.owner = at.owner
                            and atc.table_name = at.table_name) as columns)))
         ), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
  from all_tables at
 where owner = 'SCOTT'

which returns an XML document like so :

<?xml version="1.0" standalone="yes"?>
<Tables>
  <Table TABLE_NAME="DEPT">
    <OWNER>SCOTT</OWNER>
    <TABLESPACE_NAME>USERS</TABLESPACE_NAME>
    <STATUS>VALID</STATUS>
    <NUM_ROWS>4</NUM_ROWS>
    <COLUMNS>
      <Column COLUMN_NAME="DEPTNO">
        <DATA_TYPE>NUMBER</DATA_TYPE>
        <DATA_LENGTH>22</DATA_LENGTH>
        <DATA_SCALE>0</DATA_SCALE>
        <DATA_PRECISION>2</DATA_PRECISION>
        <NULLABLE>N</NULLABLE>
      </Column>
      <Column COLUMN_NAME="DNAME">
        <DATA_TYPE>VARCHAR2</DATA_TYPE>
        <DATA_LENGTH>14</DATA_LENGTH>
        <NULLABLE>Y</NULLABLE>
      </Column>
      <Column COLUMN_NAME="LOC">
       <DATA_TYPE>VARCHAR2</DATA_TYPE>
       <DATA_LENGTH>13</DATA_LENGTH>
       <NULLABLE>Y</NULLABLE>
     </Column>
    </COLUMNS>
  </Table>
</Tables>

etc etc.
I hope this is helpful to someone.  Most of the documentation that supports this post can be found here.

Occasionally it may be useful to have a primary / foreign key relationship between a view and table, where the view is the primary key referenced by a column in the table.

Oracle doesn't let you do this quite as 'completely' as we'd probably like, but it at least let's you put in the building blocks which may help the CBO and make your database design more concise or elegant in places.

Here is a quick example :

SQL> create view pk_view
(item_id, item_name,
constraint pk_test_view primary key (item_id) rely disable novalidate)
as
select 1, 'One' from dual
union
select 2, 'Two' from dual
union
select 3, 'Three' from dual;
View created.
SQL> create table fk_view (
item_id number references pk_view disable novalidate);
Table created.
SQL>

There, that's it.  Hope this helps someone as the create view syntax can be a little tricky.

Just a simple one but a syntax that I can never remember when I need to so a quick post may help jog mine, and your, memory.  This grep syntax is for matching lines from a text file that begin with a particular word or expression :

$ grep -w '^Word' input_file.txt > output_file.txt