ActiveRecord set field to SQL expression

Question

I am using Ruby 1.8.7 and Rails 2.3.8 and have a number of locations represented by a latitude and a longitude. I am using the SQLite SpatiaLite extension to convert the coordinates to the well-known binary (WKB) format. My concern is how to have ActiveRecord perform the insert or update, calling the appropriate SpatiaLite conversion methods. I am looking to do something like this:

obj.geometry = "AsBinary(MakePoint(4, 51))"
obj.save
# ActiveRecord would now do something like:
# INSERT INTO objects (geometry) VALUES (AsBinary(MakePoint(4, 51)))

Is this possible with ActiveRecord?

I have already tried GeoRuby but while its able to properly read WKB blobs, it is unable to save them to the same format SpatiaLite does.

Thanks!

A note: SQLite is not suitable for production, so I would recommend switching to PostgreSQL with the PostGIS extension. — Marnen Laibow-Koser, Nov 11 '11 at 15:03
@MarnenLaibow-Koser Unfortunately I don't have that luxury at the moment. I need to get this up and running with SQLite for the time being. — Laurens, Nov 14 '11 at 10:13
Not a luxury. If you don't have time to install a real multiuser DB, then you don't have time to develop this. Sorry to be harsh, but that's just the way it is. — Marnen Laibow-Koser, Nov 14 '11 at 14:42
@MarnenLaibow-Koser It's not matter of installing another database, it's matter of keeping a lot of other software that explicitly depends on SQLite in working order. Whether or not that was a practical decision in the past, these are simply the constraints I have to deal with now. — Laurens, Nov 15 '11 at 07:22
SQLite is unsuitable for production Web applications, due to its lack of concurrency support. If your production Web application is depending on it, it is a disaster waiting to happen. — Marnen Laibow-Koser, Nov 15 '11 at 14:56

score 0 · Answer 1 · answered Nov 11 '11 at 08:02

0

I didn't use GeoRuby yet, but for spatial data I used thinking-sphinx with @geodist function, and it's quite nice to use tool.

Some resources you can read about it:

answered Nov 11 '11 at 08:02

Michał Czapko

1,948
1
16
25

Hi Michal, thanks for the swift reply! While Thinking Sphinx works great for searching, I am trying to insert data in a very specific format here. As far as I can tell, Thinking Sphinx does not do that, does it? – Laurens Nov 11 '11 at 08:54
You said that you keep location information in latitude and longitude - with Sphinx you have these fields too, but as far as I know there is no support for querying for GeoRuby Point objects. – Michał Czapko Nov 11 '11 at 09:46
I indeed have a set of latitudes and longitudes, but I need to convert them to the well-known binary format. I updated my question and hope its more clear now what I am trying to achieve. – Laurens Nov 11 '11 at 10:39
For MySQL or PostgreSQL there is a spatial_adapter gem (https://github.com/fragility/spatial_adapter), but again I don't see any equvalent tool for SQLite. – Michał Czapko Nov 11 '11 at 10:51

score 0 · Accepted Answer · answered Nov 14 '11 at 12:13

After days of searching and coming up with no solution, I decided to simply leverage the raw connection to perform the actual insert:

rc = ActiveRecord::Base.connection.raw_connection
rc.execute("INSERT INTO objects (geometry)
            VALUES (MakePoint(#{obj.lng}, #{obj.lat}))")

ActiveRecord set field to SQL expression

2 Answers2