If we take that approach as a given, then the geo features we need in any backend are really pretty basic.

On Mon, Jul 16, 2012 at 8:29 PM, Brian Herbert <brian@...> wrote:

Pablo (and all),

The reason there's a separate location table is exactly why you thought there was a location table. When Ushahidi was first being written, the idea was to reuse locations as places.

The problem I think with storing all of the lat,lon data in points is it makes the queries a bit more complex, which can't be abstracted out in Kohana's ORM. From the direction this discussion is going though, it looks like some kind of support for a "geo ORM" might be necessary.

Brian
On Tuesday, July 17, 2012 at 8:44 AM, Aaron Huslage wrote:
With postgis tables the Geometric types are very much more efficient in both storage and searching. GIST indexes provide a fabulous method for quick retrieval. If we could get even half of that optimization into mySQL that would be a boon.

--
Aaron Huslage
sent via mobile device

On Jul 16, 2012, at 7:24 PM, "Pablo A. Destéfanis" <pdestefanis@...> wrote:

I’ve been wondering that for a *long* time J My conclusion is that it’s just a legacy bit of design, where maybe the system was expecting several reports from each location, rather the more common 1-1 relationship we have now. (Original devs, you may now laugh at my conclusion…)

What I usually end up doing is storing POINT(longitude,latitude), as to work with a geometry for each location (which is to say, for each incident). So we can add to the question, why do we save lat/lon, when we could use a geometry? (and derive lat/lon as needed).

We now have one geom-enabled table, to store geometries associated with incidents. Having this optional 1-many relationship between a report and several locations could make sense.

If anyone has any info on performance of indexed geometry fields vs the traditional lat/long, please comment. I assume that for simple lookups, the floats will be faster.

Pablo

From: John Etherton [mailto:john.etherton@...]
Sent: Monday, July 16, 2012 17:12
To: developers@...
Subject: Re: [ushahidi developers] PHP/MySQL

For that matter does it make sense to store locations in separate tables from the incidents?

On 07/16/2012 05:01 PM, David Kobia wrote:

For starters,
Just testing last week, I noticed quite a difference between
- `longitude` float(10,6) NOT NULL DEFAULT '0',

- `latitude` float(10,6) NOT NULL DEFAULT '0',

and the current
- `latitude` double NOT NULL DEFAULT '0',
- `longitude` double NOT NULL DEFAULT '0',

Doubles are twice as large and extremely wasteful. Also, there really
isn't any need to know within a millimeter, the latitude/longitude. 6
decimal places will more than suffice (XXXX.XXXXXX). Combine this with

indexing and we might be getting somewhere.

On Mon, Jul 16, 2012 at 6:25 PM, Pablo A. Destéfanis
<pdestefanis@...> wrote:

I agree with Emmanuel, and let me add:

·         While PostGIS is excellent, quite a lot can be done with MySQL’s

spatial extensions. I don’t swear over it, but the complexity of porting the
app needs to be weighed against the limitations (also known as the “how many
ppl would need it”).

·         If you have a big site, you can always offload your data to your
DB of choice. To me that is part of the “a factory installation won’t cut
it”

·         Not sure how the NoSQL option will.

·         I do not have info on slow queries with me, but I’m sure code
could be tightened. While I can’t point to examples, I do recall this from
looking at several query-logs in MySQL.

Last, if anyone is interested in testing this, I would love to stay in
touch.

Cheers,

Pablo

From: Emmanuel Kala [mailto:emkala@...]
Sent: Monday, July 16, 2012 14:50
To: developers@...

Subject: Re: [ushahidi developers] PHP/MySQL

Some notes:

Scale is a function of design
MySQL scales quite well...when you know what you're doing but it's not very

inspiring for geo-spatial work. PostgreSQL (+PostGIS) has clearly become the
preferred choice for such.
For high traffic deployments, a factory installation won't cut it - some
load balancing, caching, DB tuning etc work has to be done in addition to

exorcising ghosts that are always inherent in the machine
The NoSQL options, e.g. Redis, are pretty good for caching (Reddit and big
xxx sites)
Profiling code has provided great insights on what sections of the

application are yielding beastly load times

Thanks.

On Mon, Jul 16, 2012 at 10:59 PM, João Peixoto <joao.mpfp@...> wrote:

Hello all,

First post here, not really an Ushahidi developer yet, for the moment the
NGO I work with has a couple projects that use it and since we love it,

we're starting customization to meet our needs (which differ a bit from the
typical Crwodmap sites - it's a biking support site).

On Mon, Jul 16, 2012 at 4:51 PM, David Kobia <david@...> wrote:

[...]

An option might be to ensure that database interaction is abstracted
enough that it doesn't matter what the underlying database is... and

this is something that might be worth looking into.

I'd love to see some DB abstraction on Ushahidi. If possible, have a "quick
and dirty" approach using mysql that allows a quick deployment, but as a

site scales up and requires further optimizations, allow better back-ends
for efficiency.

My 2 cents!

JP

--

Kind Regards,

Emmanuel Kala

Skype: emmanuel.kala

Judgement comes from experience, experience comes from poor judgement

~~~~~~~~~~~~~~~~~~~~~~~~~~
List Archive: http://list.ushahidi.com/

Would you like to receive list mail batched in a daily digest instead? Send a message to:
developers-digest-subscribe@...

To remove your address from the list, just send a message to
the address in the "List-Unsubscribe" header of any list
message. If you haven't changed addresses since subscribing,

you can also send a message to:
developers-unsubscribe@...

For addition or removal of addresses, we'll send a confirmation

message to that address. When you receive it, simply reply to it
to complete the transaction.

If you need to get in touch with the human owner of this list,
please send a message to:

developers-owner@...

--
Aaron Huslage
http://blog.hact.net
IM: AIM - ahuslage; GTalk - huslage@...; Skype - huslage