Hybrid search with real time translation. Combining pgvector, postgis, azure_local_ai, azure_ai, Azure Language Services

search.sql

-- Hybrid search with real time translation. Combining pgvector, postgis, azure_local_ai, azure_ai, Azure Language Services
WITH listings_cte AS (
    SELECT l.listing_id, name, listing_location, summary FROM listings l
    INNER JOIN calendar c ON l.listing_id = c.listing_id
    WHERE ST_DWithin(
        listing_location::geography,
        ST_GeographyFromText('POINT(-122.349358 47.620422)')
        ,3218 )
    AND c.date = '2016-01-13'
    AND c.available = 't'
    AND c.price <= 75.00
    AND l.listing_id IN (SELECT listing_id FROM listings)
    ORDER BY local_description_vector <=> azure_local_ai.create_embeddings('multilingual-e5-small:v1', 
     'properties in Seattle near the Space Needle with 3 bedrooms, and allow pets')::vector
    LIMIT 5
)
SELECT
    l.listing_id,
    name,
    listing_location,
    l.summary,
    azure_cognitive.translate(summary, 'es') AS translated_summary
FROM listings_cte l;