Dubai Land Department Analytics API

A comprehensive Node.js API that processes natural language queries about Dubai real estate data and returns structured responses optimized for Chart.js visualization.

Features

• Natural Language Processing: Parse complex queries using NLP libraries
• Chart.js Integration: Generate data formatted for frontend visualization
• Multiple Query Types: Support for trends, comparisons, summaries, and analytics
• Dubai-Specific: Tailored for Dubai real estate terminology and areas
• RESTful API: Clean, well-documented endpoints

Quick Start

Prerequisites

• Node.js 16+
• MySQL 8.0+
• Dubai Land Department database schema

Installation

1. Clone and install dependencies:

npm install

2. Set up environment variables:

cp .env.example .env
# Edit .env with your database credentials

3. Set up the database:

# Import the provided SQL schema
mysql -u root -p < 2.sql

4. Start the server:

# Development
npm run dev

# Production
npm start

API Endpoints

Main Query Endpoint

POST /api/query
Content-Type: application/json

{
  "query": "Give me the last 6 months rental price trend for Business Bay"
}

Predefined Queries

• GET /api/queries/rental-trend/:area - Rental trends for specific area
• GET /api/queries/top-areas - Top areas with most transactions
• GET /api/queries/project-summary - Project summaries
• GET /api/queries/commercial-leasing - Commercial leasing analysis
• GET /api/queries/residential-leasing - Residential leasing analysis

Utility Endpoints

• GET /health - Health check
• GET /api/database/info - Database statistics
• GET /api/queries/available - Available query types

Supported Query Types

Rental Analysis

• "Give me the last 6 months rental price trend for Business Bay"
• "Summarise by week" (refinement)
• "Apartments only" (further refinement)

Project Analysis

• "Brief about the Project"
• "List of fast moving projects in last 6 months"
• "Which area is seeing uptick in off-plan projects in last 6 months"

Area Performance

• "Which area is having more rental transactions?"
• "Top 5 areas for Commercial leasing and why?"
• "Top 5 areas for Residential leasing and why?"
• "Avg price of 3BHK apartment by area in last 6 months"

Response Format

{
  "success": true,
  "data": {
    "text": "Rental price trend for Business Bay over the last 6 months",
    "visualizations": [
      {
        "type": "line",
        "title": "Monthly Rental Price Trend",
        "data": {
          "labels": ["2024-01", "2024-02", "2024-03"],
          "datasets": [{
            "label": "Average Rental Price (AED)",
            "data": [85000, 87000, 89000],
            "borderColor": "rgb(75, 192, 192)",
            "backgroundColor": "rgba(75, 192, 192, 0.2)"
          }]
        }
      }
    ],
    "cards": [
      {
        "title": "Average Price",
        "value": "92,500 AED",
        "subtitle": "Last 6 months",
        "trend": "+15.3%"
      }
    ],
    "sql_queries": ["SELECT ..."],
    "metadata": {
      "query": "original query",
      "intent": "trend",
      "result_count": 6
    }
  }
}

Chart.js Integration

The API returns data in Chart.js compatible format:

• Line Charts: For time series trends
• Bar Charts: For area comparisons
• Pie Charts: For distribution analysis
• Cards: For summary statistics

Database Schema

The API works with these main tables:

• transactions - Real estate transactions
• rents - Rental contracts (Ejari)
• projects - Development projects
• buildings - Building registry
• lands - Land registry
• valuations - Property valuations
• brokers - Real estate brokers

Development

Project Structure

src/
├── controllers/     # Request handlers
├── services/        # Business logic
│   ├── nlpService.js      # NLP processing
│   ├── sqlGenerator.js    # SQL generation
│   └── chartFormatter.js  # Chart.js formatting
├── models/          # Database models
├── middleware/      # Express middleware
├── routes/          # API routes
└── utils/           # Utility functions

Testing

# Test predefined queries
curl http://localhost:3000/api/queries/top-areas

# Test custom query
curl -X POST http://localhost:3000/api/query \
  -H "Content-Type: application/json" \
  -d '{"query": "Give me the last 6 months rental price trend for Business Bay"}'

Environment Variables

# Server Configuration
PORT=3000
NODE_ENV=development

# Database Configuration
DB_HOST=localhost
DB_PORT=3306
DB_NAME=dubai_dld
DB_USER=root
DB_PASSWORD=your_password

# API Configuration
RATE_LIMIT=100

License

MIT License - see LICENSE file for details.