1

I'm writing a client-server application where geotagged photos are main content.

Backend is based on ASP.NET Core tech stack.

Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

I'm not really sure if i need relational DB for such a simple task. Also i'd love to see search by distance from point made by DB not by my code. Good integration with .NET Core is a great plus.

I've worked with SQL Server from MSFT, but it seems a bit overweight for this simple task.

Could you guys with some experience in area, advise a DB for my task?

  • 3
    Postgres + PostGIS might fit your need. Postgres also has an image manipulation extension, and you can even find images by similarity. Can use pg_trgm for autocomplete – Neil McGuigan Apr 12 '17 at 19:26
  • 1
    To add onto @Neil's comment, the syntax for Postgres is pretty close to that found in SQL Server as well, so your learning curve won't hopefully be as significant. – John Eisbrener Apr 12 '17 at 19:52
0

PostgreSQL

Go check out the book PostGIS in Action Second Eddition.

PostGIS has everything you're requesting.

Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

CREATE TABLE photos (
  photoid  serial        PRIMARY KEY,
  path     text,
  tags     text[],
  location geography,
  meta     jsonb
);

INSERT INTO photos(path,tags,location, meta)
VALUES
  (
    '/foo/asdf.jpg',
    ARRAY['person', 'america', 'male'],
    ST_MakePoint(5.493,3.2948),
    '{"CAMERA":"NIKON"}'::jsonb
   );

SELECT *
FROM photos
WHERE tags @> ARRAY['person'];

SELECT *
FROM photos
WHERE ST_DWithin(ST_MakePoint(0,0)::geography, location, 10000);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.